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.
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
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.
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,