Skip to main content


Showing posts from September, 2013

How to generate scripts to alter location of all databases

This is is a handy piece of TSQL code which generates a list of TSQL commands that can be used to alter the location of all user databases to the path specified. It assumes that the filenames for the data file and log file will match the database name plus ".mdf" for the former and "_log.ldf" for the latter.

This can be executed while the SQL service is running and will only take effect after it has been restarted. You will of course have to physically copy the files to the new location when the service is down.

USEmaster; GO
SELECT 'ALTER DATABASE ['+name +'] MODIFY FILE ( NAME = ['+ name+'], FILENAME = ''F:\MSSQL\Data\'+name+'.mdf'' ); ALTER DATABASE ['+name +'] MODIFY FILE ( NAME = ['+name+'_Log], FILENAME = ''E:\MSSQL\Log\'+name+'_Log.ldf'' ); GO '
FROMsysdatabases WHERE[sid]<> 0x01 ORDERBYname

Show every user from every database

If you need to know which users exist in every database on your server (as apposed to server logins) you can use the undocumented stored procedure sp_msforeachdb as in the example below:

EXECsp_msforeachdb'USE [?];                      SELECT DB_NAME() As DBName                            ,name                      FROM sysusers                      ORDER BY name'

This piece of code is ideal for a Snippet.

Show the owner of a SQL Agent Job

This is a very handy piece of code that I have readily available. It is useful to use on servers that have dozens of SQL Agent jobs to find out who owns a job and whether it is enabled.

By default when someone creates a job it sets their name as the owner which means if their account becomes disabled, they have left the company for instance, the job will no longer work. I've seen exactly this issue many times over the years. So every now and again I will run this piece of code to find who the owner is and then ask them to assign it to a more suitable account.

USEMSDB; GO SELECTj.NAMEASJobName ,u.NAMEASJobOwner ,j.enabled FROMsysjobsj LEFTJOINmaster.sys.sysloginsu ONj.owner_sid=u.sid ORDERBYj.NAME
This piece of code is ideal for a Snippet.