Finding All Databases Where User is not in Role

This script will find all the databases (matching a certain pattern) on the server where a given user is not a member of a given role. Because sometimes you need to know that.

-- Be sure to change the User and Role variables. 
declare @Sql      nvarchar(4000)    set @Sql = ''
declare @UserName sysname           set @UserName = 'Larry'
declare @RoleName sysname           set @RoleName = 'db_owner'

set @Sql = 'USE [?]

            IF ''?'' LIKE ''MYDATABASE%''
            BEGIN
                IF NOT EXISTS (SELECT * 
                                 FROM sys.database_role_members
                                WHERE USER_NAME(role_principal_id)   = ''' + @RoleName + '''
                                  AND USER_NAME(member_principal_id) = ''' + @UserName + ''')
                BEGIN
                    RAISERROR(''Database %s - user %s not in role %s'', 10, 1, ''?'',
                              ''' + @UserName + ''', ''' + @RoleName + ''') WITH NOWAIT
                END
            END'

exec sp_msforeachdb @Sql

CMS: Find All Databases in all Servers that don't have a Specified Table

This code is for CMS (Central Management Servers), and displays all the databases on all the servers that don't have a specified table.

-- For all user databases in all servers, which ones don't have a "Customer" table? 
set nocount on

declare @Schema sysname     set @Schema = 'dbo'
declare @Table  sysname     set @Table  = 'Customer'
declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

    if db_id() > 4
        if object_id(N''' + @Schema + '.' + @Table + ''', N''U'') is null
            print @@SERVERNAME + '': '' + ''?'' + '' - ' + @Schema + '.' + @Table + ' does not exist'''

exec sp_msforeachdb @Sql

Adding a User to Multiple Databases

Everyone seems to have their own version. Mine is as simple as I could make it. Make sure you create the LOGIN first, and set the @User variable in the code below.

Assumes you have already created a login named @User . 
set nocount on

declare @User sysname
set @User = 'trespasserswill'                  -- Change this!! 

declare @Sql nvarchar(4000)

set @Sql =
'
    use [?]

    if db_id() > 4
    begin
        print ''?''
        create user ' + quotename(@User) + ' for login ' + quotename(@User) + '
        exec sp_addrolemember ''db_datareader'', ''' + @user + '''
        exec sp_addrolemember ''db_datawriter'', ''' + @user + '''
    end
'

exec sp_MSForEachDB @Sql

Find all Occurrences of Text in all Stored Procedures in all Databases

Sometimes you'd like to find all occurrences of a piece of text in every stored procedure, trigger, etc., possibly to replace it with a new value. The script below will do just that; it returns the database name, the object name, and the object type. Note that it works for both SQL Server 2000 and post-SQL Server 2000 versions.

One thing to note is that the @TextToFind uses the LIKE operator with its ESCAPE argument set to the dollar sign symbol. I added the ESCAPE argument just to make it easier to search for text such containing the underscore character, such as 'My_HardToFind_Thing". Of course, if you're searching for text with a dollar sign symbol in it, you'll want to change the ESCAPE argument to something else.

(The script below can also be run in CMS, which will allow you to also return results across server groups.)

-- Simply set the text you want to look for here. 
declare @TextToFind nvarchar(4000)
set @TextToFind = 'THIS IS MY TEXT TO FIND'

declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

     declare @DatabaseName sysname
     declare @ProcName     sysname
     declare @ProcType     nvarchar(60)

     if left(convert(nvarchar, serverproperty(''productversion'')), 1) <> ''8''
     begin
         declare cur cursor local fast_forward for
            select distinct ''?''          as ''Database Name''
                          , o.name         as ''Object Name''
                          , o.type_desc    as ''Object Type''
              from sys.sql_modules   m
              join sys.objects       o
                on m.object_id = o.object_id
             where m.definition like ''%' + @TextToFind + '%'' escape ''$''

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    else
    begin
         declare cur cursor local fast_forward for
            select distinct ''?''               as ''Database Name''
                          , object_name(c.id)   as ''Object Name''
                          , o.xtype             as ''Object Type''
              from syscomments   c
              join sysobjects    o
                on c.id = o.id
             where c.[text] like ''%' + @TextToFind + '%'' escape ''$''
               and objectproperty(c.id, ''isprocedure'') = 1

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    
    '

exec sp_msforeachdb @Sql

Run DBCC CHECKDB Against All Databases on Server

This script runs DBCC CHECKDB against every database on the server. It starts off with the simplest and fastest commands, and works up the the most stringent test. Note that TABLOCK keyword is commented out - you can use it at your own discretion. The commands that actually repair the database can't be run accidentally, and the version that can allow data loss - aptly named REPAIR_ALLOW_DATA_LOSS - is left as an exercise for the reader. (In other words, I'm not giving you that particular big, sharp knife!)

-- Run DBCC CHECKDB commands against all databases, including system. 
-- Read-only checks are run automatically. For safety's sake, to run 
-- the "repair" checks, you have to highlight that section and run it. 
-- SQL Server 2005+ 

set nocount on
declare @Sql nvarchar(4000)


--------------------------------------- 
--           READ-ONLY CHECKS           
--------------------------------------- 

-- Do quickest check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* ?? TABLOCK, */ PHYSICAL_ONLY, ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now check things beside what PHYSICAL_ONLY does: the integrity of the 
-- physical structure of the page and record headers, and the consistency 
-- between the pages' object ID and index ID and the allocation structures. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', NOINDEX) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- Now also check the indexes. This is the most thorough check possible. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'') WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

go


--------------------------------------- 
--           READ-WRITE CHECKS          
--                                      
-- "Use the REPAIR options only as a    
-- last resort. To repair errors, we    
-- recommend restoring from a backup."  
--------------------------------------- 

raiserror('    *** THESE ARE READ-WRITE CHECKS - BE SURE YOU KNOW WHAT YOU ARE DOING!', 16, 1) with nowait

set nocount on
declare @Sql nvarchar(4000)

-- Performs both minor, quick repairs, such as repairing extra keys in 
-- nonclustered indexes, and time-consuming repairs such as rebuilding 
-- indexes. These repairs can be performed without risk of data loss. 
set @Sql = 'USE [?]
            RAISERROR(''--------------------------------------------- ?'', 10, 1) WITH NOWAIT   
            DBCC CHECKDB (''?'', REPAIR_REBUILD) WITH /* TABLOCK, */ ALL_ERRORMSGS '

exec sp_msforeachdb @Sql

-- If you want to use REPAIR_ALLOW_DATA_LOSS, you'll have to write your own command! 

go

CMS: Find, in All Servers and Databases, if given Table.Column's data is unique

Using CMS, we can determine if all the Customer.CustomerID values are unique, and therefore, for example, a candidate for being made a primary key.

-- Find, in All Servers and Databases, if given Table.Column's data is unique. 
set nocount on

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare @Schema sysname     set @Schema = 'dba'
    declare @Table  sysname     set @Table  = 'Customer'
    declare @Column sysname     set @Column = 'CustomerId'

    declare @Sql nvarchar(4000)

    set @Sql = 'use [?]

                if  exists (select *
                              from sys.tables
                             where name = ''' + @Table + '''
                               and schema_id(''' + @Schema + ''') = schema_id)
                begin
                    declare @NonUniqueCount int
                    
                    select @NonUniqueCount = count(*)
                      from ' + @Schema + '.' + @Table  + '
                  group by ' + @Column + '
                    having count(*) > 1

                    if @NonUniqueCount is not null
                    begin
                        print @@SERVERNAME +'': '' + ''?'' + '' - @NonUniqueCount: '' + cast(@NonUniqueCount as nvarchar(50))
                    end
                end'

    exec sp_msforeachdb @Sql
end

CMS: Find all Databases on all Servers with Table "A" without Column "B"

This CMS (Central Management Server) code will tell you, for all databases in all servers, which tables of a given name don't have a column of a given name.

-- For all databases in all servers that have a "Customer" table, which ones don't have a "ZipCode" column? 
set nocount on

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare @Schema sysname     set @Schema = 'dbo'
    declare @Table  sysname     set @Table  = 'Customer'
    declare @Column sysname     set @Column = 'ZipCode'

    declare @Sql nvarchar(4000)

    set @Sql = 'use [?]

                if exists (select *
                            from sys.tables
                           where name = ''' + @Table + '''
                             and schema_id(''' + @Schema + ''') = schema_id)
                begin
                    if not exists (select *
                                     from sys.columns
                                    where name = ''' + @Column + '''
                                      and object_name(object_id) = ''' + @Table + ''')
                    begin
                        print @@SERVERNAME + '': '' + ''?'' + '' - ' + @Table + ' table has no ' + @Column + ' column''
                    end
                end'

    exec sp_msforeachdb @Sql
end

Use Central Management Servers to Alter a Column in All Servers

The "Central Management Servers" functionality of SQL Server Management Studio is an excellent way to apply the same code to every server. By "every server", I mean groups of servers that you define: you might have different servers in folders named "Production", "Development", "QA", "SQL Server 2000", etc. It just depends on your needs.

To get to the "Central Management Servers" pane in SSMS, simply select the "View, Registered Servers" menu item. After you've set up your servers, you can execute queries against every database in every server. The example below changes the width of a column in a table. Note that in this example, we look for only databases whose name begins with "DB100", we exclude read-only databases, and we specify the schema, table, and column name we want to modify. The code also checks that the length isn't already set to the value we want, to avoid unnecessary noise in the output. This code is compatible with SQL Server 2000 and later (hence the use of "syscolumns", etc.)

-- Comment. 
set nocount on
declare @Sql nvarchar(4000)

set @Sql = 'use [?]

            if exists (select *
                         from syscolumns   sc
                         join sysobjects   so
                           on sc.id = so.id
                        where ''?''                                   like ''DB100%''
                          and databaseproperty(''?'', ''IsReadOnly'')    = 0
                          and user_name(so.uid)                          = ''dbo''
                          and object_name(sc.id)                         = ''Customer''
                          and sc.name                                    = ''FirstName''
                          and sc.length                                 != 120)
            begin
                select ''?'', sc.length
                  from syscolumns   sc
                  join sysobjects   so
                    on sc.id = so.id
                 where ''?''                                   like ''DB100%''
                   and databaseproperty(''?'', ''IsReadOnly'')    = 0
                   and user_name(so.uid)                          = ''dbo''
                   and object_name(sc.id)                         = ''Customer''
                   and sc.name                                    = ''FirstName''
                   and sc.length                                 != 120

                ALTER TABLE dbo.Customer ALTER COLUMN FirstName VARCHAR(120) NULL
            end '

exec sp_msforeachdb @Sql
go

References

For more information on Central Management Servers:

     Create a Central Management Server and Server Group

     Registered Servers and Central Management Server Stores

     Execute SQL Server query on multiple servers at the same time

A quick script to display the total size each database, taking into account multiple MDF and LDF files.

-------------------------------------------------------------------------------
-- Returns the total size of all databases' mdf and ldf files, and the grand   
-- total of the two.                                                           
-------------------------------------------------------------------------------

-- Create the temp table. 
IF OBJECT_ID('tempdb..##t') IS NOT NULL
    DROP TABLE ##t

CREATE TABLE ##t 
(
    [Database]   sysname, 
    [File Type]  NVARCHAR(50),
    [Size (MB)]  BIGINT
)

-- Populate the temp table with value for all the mdf and ldf files for all databases.  
DECLARE @CrLf CHAR(2)
SET @CrLf = CHAR(13) + CHAR(10)

DECLARE @Sql NVARCHAR(MAX)

SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , type_desc '                                            + @CrLf
         + '         , CAST(Size AS BIGINT) * (8 * 1024) / 1024 / 1024 '      + @CrLf
         + '      FROM sys.database_files '

EXEC sp_msforeachdb @Sql

-- Calculate the grand total (mdf files' sizes + ldf files' sizes; add to temp table. 
SET @Sql = 'USE ? '                                                           + @CrLf
         + ''                                                                 + @CrLf
         + 'INSERT ##t '                                                      + @CrLf
         + '    SELECT ''?'' '                                                + @CrLf
         + '         , '' - TOTAL - '' '                                      + @CrLf
         + '         , CAST(SUM(Size) AS BIGINT) * (8 * 1024) / 1024 / 1024 ' + @CrLf
         + '      FROM sys.database_files '
 
EXEC sp_msforeachdb @Sql

-- Display the totals for each database and file type. 
  SELECT [Database], [File Type], SUM([Size (MB)])   AS 'Size (MB)'
    FROM ##t
GROUP BY [Database], [File Type]
ORDER BY [File Type] DESC, [Size (MB)] DESC

IF OBJECT_ID('tempdb..##t') IS NOT NULL
    DROP TABLE ##t

(Practically) Fool-Proof Backups

A script and batch file that practically anyone can run, no matter how non-technical. The batch file prompts you for backup path, server, and instance, and then calls the SQL script.

: -----------------------------------------------------------------------------
: Prompts user for info and calls the SQL script to do the backup.             
: -----------------------------------------------------------------------------
: Copyright 2010  Larry Leonard, Definitive Solutions Inc.                     
:                 http://www.DefinitiveSolutions.com                           
:                                                                              
: Copying and distribution of this file, with or without modification, are     
: permitted in any medium without royalty provided the copyright notice and    
: this notice are preserved.  This file is offered as-is, without any          
: warranty.                                                                    
: -----------------------------------------------------------------------------

@echo off
prompt $g
@echo.
@echo %date% %time%
@echo.


: -----------------------------------------------------------------------------
: Display free disk space.
: -----------------------------------------------------------------------------

for /f "tokens=3" %%a in ('dir ^ find /i "bytes free"') do set freeMB=%%a
set freeMB=%freeMB:~0,-8%
@echo Disk Space Free: %freeMB% MB
@echo.


: -----------------------------------------------------------------------------
: Prompt the user.
: -----------------------------------------------------------------------------

set /p   TargetServerAndInstance=Server and Instance  ("Enter" for default)?
if     "%TargetServerAndInstance%"==""  set TargetServerAndInstance="UNICORN\SQL2008EXPRESS"

set /p   BackupFolder            =Backup Folder        ("Enter" for default)?
if     "%BackupFolder%"           ==""  set BackupFolder="C:\TEMP"


: -----------------------------------------------------------------------------
: Build command string.  For information on sqlcmd.exe, see:
:    http://msdn.microsoft.com/en-us/library/ms162773(SQL.90).aspx
:
:    -a    Requests a packet of a different size
:    -S    Server\instance
:    -i    Input script name
:    -o    Output script name
:    -w    Column width
:    -m    All headers are returned with messages
:    -b    sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs
:    -r    Redirects the error message output to the screen
:    -v    Declare $(variable_name)
:    -V    Lowest severity level sqlcmd reports
:
: -----------------------------------------------------------------------------

set SqlCmdCommand=sqlcmd.exe
if not "%TargetServerAndInstance%"=="" set SqlCmdCommand=sqlcmd.exe -S %TargetServerAndInstance%
set SqlCmdCommand=%SqlCmdCommand% -r 1 -b -m-1 -V 1 -a 32767 -w 2000
set SqlCmdCommand=%SqlCmdCommand% -i BackupAllDatabases.sql
set SqlCmdCommand=%SqlCmdCommand% -o BackupAllDatabases.txt
set SqlCmdCommand=%SqlCmdCommand% -v BackupFolder=%BackupFolder%


: -----------------------------------------------------------------------------
: Call the backup SQL script.
: -----------------------------------------------------------------------------

if not exist "@BackupFolder" mkdir "@BackupFolder"
if errorlevel 1 goto l_error

@echo on
%SqlCmdCommand%
@echo off
@echo.
if errorlevel 1 goto l_error


: -----------------------------------------------------------------------------
: Done.
: -----------------------------------------------------------------------------

goto l_done

:l_error
@echo.
@echo                             *** AN ERROR HAS OCCURRED! ***

:l_done:
@echo.
@echo %date% %time%
@echo.

Notepad.exe BackupAllDatabases.txt

pause

@echo.
prompt $p$g

The SQL script backs up all user databases and their logs, and the master and msdb system databases.

-------------------------------------------------------------------------------
-- Called by BackupAllDatabases.bat to backup all databases and their logs.
-- The path set in @sBackupFolder must exist - this script will not create it.
-------------------------------------------------------------------------------
-- Copyright 2010  Larry Leonard, Definitive Solutions Inc.               
--                 http://www.DefinitiveSolutions.com                     
--                                                                        
-- Copying and distribution of this file, with or without modification, are
-- permitted in any medium without royalty provided the copyright notice and
-- this notice are preserved. This file is offered as-is, without any warranty.
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- MUST be set as shown to support indexes on computed columns, indexed views.
SET ANSI_NULLS ON                                 -- Deprecated: leave set ON. 
SET ANSI_PADDING ON                               -- Deprecated: leave set ON. 
SET ANSI_WARNINGS ON                              -- No trailing blanks saved. 
SET ARITHABORT ON                                 -- Math failure not ignored. 
SET CONCAT_NULL_YIELDS_NULL ON                    -- NULL plus string is NULL. 
SET NUMERIC_ROUNDABORT OFF                        -- Allows loss of precision. 
SET QUOTED_IDENTIFIER ON                          -- Allows reserved keywords. 

-- These are not, strictly speaking, required, but are generally good practice.
SET NOCOUNT ON                                    -- Minimize network traffic. 
SET ROWCOUNT 0                                    -- Reset in case it got set. 
SET XACT_ABORT ON                                 -- Make transactions behave. 
-------------------------------------------------------------------------------


-- These are the only variables which you are required to provide a value for. 
DECLARE @sBackupFolder NVARCHAR(260)
SET     @sBackupFolder = 'C:\Temp'

-- Create a file-system friendly date. 
DECLARE @sDate NVARCHAR(50)
SET @sDate = CONVERT(NVARCHAR, CAST(GETDATE() AS SMALLDATETIME), 126)
SET @sDate = REPLACE(@sDate, 'T', '_')
SET @sDate = REPLACE(@sDate, ':', '-')

-- Create the commands. 
DECLARE @sSql NVARCHAR(4000)

SET @sSql =''
 + 'IF  ''?'' NOT IN (''model'', ''tempdb'') '
 + 'AND ''?'' NOT LIKE ''ReportServer$%'' '
 + ''
 + 'BEGIN '
 + ' RAISERROR(''Starting backup of database ?.'', 10, 1) WITH NOWAIT '
 + ''
 + ' BACKUP DATABASE [?] '
 + '   TO DISK = ''' + @sBackupFolder + '\?_dat_' + @sDate + '.bak '''
 + '   WITH '
 + '   NOFORMAT, '
 + '   NOINIT, '
 + '   NAME = ''? - Full Database Backup - ' + @sDate + ''', '
 + '   SKIP, '
 + '   STATS = 10; '
 + ''
 + ' IF ''?'' NOT IN (''master'', ''msdb'') '
 + ' BEGIN '
 + ' BACKUP LOG [?] '
 + '    TO DISK = ''' + @sBackupFolder + '\?_log_' + @sDate + '.bak '''
 + '    WITH '
 + '    NOFORMAT, '
 + '    NOINIT, '
 + '    NAME = ''? - Full Log Backup - ' + @sDate + ''', '
 + '    SKIP, '
 + '    STATS = 10; '
 + ' END '
 + ''
 + 'END; '

-- Run the commands for each database. 
USE master
EXEC sp_MsForEachDb @sSql

-- Done.
GO

Pretty much anyone should be able to run this.

Using sp_MsForEachDb and sp_MsForEachTable Together

Ever wanted to run a T-SQL command on every user table in every user database? Here's how to nest the undocumented sp_MsForEachTable system stored procedure inside the undocumented sp_MsForEachDB system stored procedure.

DECLARE @Sql NVARCHAR(4000) = 
      'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''''#'''' AND owner_sid = 0x01) RETURN '
    + 'RAISERROR(''''Reclaiming space on table ' + QUOTENAME('#') + '.?'''', 10, 1) WITH NOWAIT '
    + 'USE ' + QUOTENAME('#') + ' '
    + 'DBCC CLEANTABLE(''''#'''', ''''?'''', 10000) WITH NO_INFOMSGS '

SET @Sql = 'USE ' + QUOTENAME('#') + ' EXEC sp_MsForEachTable ''' + @Sql + ''''

EXEC sp_MsForEachDb @Sql, @replacechar = '#'

The trick is the @replacechar parameter to the sp_MsForEachDb stored procedure: it's how we keep the placeholders for the database and the table separate. If there's an easier way, I'd love to see it.