T-SQL Tuesday 143 - Short code examples

This month the #TSQL2SDAY invitation comes from John McCormack (blog|twitter). The T-SQL Tuesday is a monthly blogging event that was created by Adam Machanic (blog|twitter) and is maintained by Steve Jones (blog|twitter).

John invites us to write about our handy short scripts. The invitation is in this post.

Loop through

I found this method really helpful in some situations where I had to run multiple commands that were dynamically generated. In the below example it is all static, but you should get the idea.

-- create a table with commands
DROP TABLE IF EXISTS [@@@]; CREATE TABLE [@@@] ([@] int, [@@] nvarchar(400));

-- populate it with commands
INSERT INTO [@@@] VALUES 
(0, 'DROP TABLE IF EXISTS dt;'),
(1, 'CREATE TABLE dt (dt datetime);'),
(2, 'INSERT INTO dt VALUES (GETDATE());'),
(3, 'SELECT dt from dt;')

-- create a loop that will execute the commands
DECLARE @ int = 0;
DECLARE @@ int = 0;
DECLARE @@@@ nvarchar(400) = '';
SELECT @@ = MAX([@]) FROM [@@@];
WHILE @ <= @@
BEGIN
	SELECT @@@@ = [@@] FROM [@@@] WHERE [@] = @;
	EXEC sp_executesql @@@@;
	SET @=@+1;
END

Agent datetime

There was a time I needed to work with the agent jobs a lot, so there was one handy function called agent_datetime() - long story short it allows you to convert the weird datetime format in the msdb to something useful. If you want to learn more check out this article.

Tables

Prior the dbatools I used this query a lot to understand what tables are in the database. I have added database and server details as well in case I was running that on multiple servers.

	GETDATE() AS CurrentTime,
	@@SERVERNAME AS SQLName,
	DB_NAME() AS DBName,

Thanks,

Mikey