SQL Server snippets #SQLGeek

This post will be an ongoing, growing collection of SQL Server code snippets that I happen to find useful. There is no particular theme, order or categorization -- just a random collection of helpful items that I hope others will find useful, too.

My apologies if I eventually include anything particularly unique or complex without citing an original author -- please comment with links to original posts so I can verify and add citations accordingly.

Read on for the snippets...

PRINT MESSAGES INSIDE A WHILE LOOP
If you want to visually track progress of a WHILE loop you can force output to the "Messages" tab.  Normally SSMS will cache any output during loop execution and display only after the operation has completed.  The "RAISERROR" command with "NOWAIT" option forces immediate output to the "Messages" tab:

DECLARE @myMessage nvarchar(max)
DECLARE @count int

SET @count = 1

WHILE (@count <= 10)
BEGIN
  SET @myMessage = 'The count is: '+cast(@count as nvarchar(10))

  RAISERROR (@myMessage,0,1) WITH NOWAIT

  SET @count = @count + 1
  WAITFOR DELAY '00:00:01'

END




PAUSE OPERATION (SLEEP)
This is a pretty straightforward command to add a delay before proceeding to the next line of SQL code.  I'm including it in my "snippets" post mainly to note that there two ways to specify time values.  The typical way is HOURS:MINUTES:SECONDS (hh:mm:ss) such as:

WAITFOR DELAY '00:00:05'


This will pause for 5 seconds.

If you wanted to pause for 5-and-a-half seconds you would use the following:

WAITFOR DELAY '00:00:05:500'


Because the fourth position of values is milliseconds and there are 1,000 milliseconds in a second, we must use a value of '500' to equal half of one second.  You can, however, mix in floating point values in the WAITFOR command as follows:

WAITFOR DELAY '00:00:5.5'


This also pauses for 5-and-a-half seconds.  The ability to use floating point values can greatly simplify your code if you are dynamically determining WAITFOR values.



COPY SQL USER ACCOUNTS FROM ONE SQL SERVER INSTANCE TO ANOTHER
When copying databases to a new SQL Server instance, any SQL user accounts must also be copied.  Copying/restoring a database to a new SQL instance and then manually reproducing accounts isn't enough as the account SIDs (security IDs) will not match between two different systems despite those accounts' names matching.  The manual approach would require deleting the accounts from the "Security/Users" section of the newly-copied database, creating the accounts on the new SQL instance, and then reapplying the accounts to the copied database.

To save you from manually creating SQL accounts and dropping/adding those accounts accounts from the copied database, the following script dynamically creates the needed "create login" scripts  for each SQL account (other than the "SA" account) with the matching SIDs:


-- SQL create user accounts
select 'create login [' + p.name + '] ' +
  case when p.type in('U','G') then 'from windows ' else '' end +
  'with ' +
  case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
  'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_policy = ' +
   case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
   case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
  else '' end +
  'default_database = ' + p.default_database_name +
  case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end

from sys.server_principals p
left join sys.sql_logins l
on  p.principal_id = l.principal_id
left join sys.credentials c
on  l.credential_id = c.credential_id
where p.type in('S','U','G')
and  p.name <> 'sa'


Tip:  To get CREATE ACCOUNTS scripts for only certain accounts (instead of ALL the accounts on a SQL instance), add a LIKE clause to the end of the above SQL script.  I, for example, like to prepend the name of my DB to any accounts that are specific to that DB.  If my DB is named "FileDownloads" and my SQL user accounts are "FileDownloads_Admin", "FileDownloads_Process", and "FileDownloads_ReadOnly", then I would add the following clause to the end of the above script:

and p.name LIKE 'FileDownloads_%'


The result is three separate lines of "CREATE ACCOUNT" SQL code for the three SQL accounts with names starting with "FileDownloads_".


The above script is over a decade old and has been shared in *many* places but rarely with any attribution.  To the best of my knowledge, the original author is Chad Boyd from MSSQLTips.