Useful SQL Server Scripts

Get the Version of SQL Server

Useful for telling what version of SQL Server is being run, including which service pack has been applied.

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'),
SERVERPROPERTY ('edition')

How Much Space Per Table

For each table in the current database, how much space is being used?

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Count All Table Records

Count the number of rows in each table in a database. How to use sysindexes and sysobjects.

SELECT so.[name] as [table name], CASE WHEN si.indid between 1 and 254
THEN si.[name] ELSE NULL END AS [Index Name], si.rowcnt
FROM sysindexes si INNER JOIN sysobjects so ON si.id = so.id
WHERE si.indid < 2 AND so.type = 'U' AND so.[name] != 'dtproperties'
ORDER BY so.[name]

List All SQL Databases

A simple example of cursors and using the master database catalog to find all databases on the server.

Declare @database varchar(255)
Declare DatabaseCursor Cursor
Local
Static
For
select name from master.dbo.sysdatabases
open DatabaseCursor
fetch next from DatabaseCursor into @database

while @@fetch_status = 0
begin
	print 'database:' + @database
	fetch next from DatabaseCursor into @database
end

close DatabaseCursor

deallocate DatabaseCursor

Reindex All Indexes on All Tables in a Database

Run this in the database you want to reindex.

DECLARE @tn varchar(255)

DECLARE tc CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN tc

FETCH NEXT FROM tc INTO @tn
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@tn,' ',90)
FETCH NEXT FROM tc INTO @tn
END

CLOSE tc

DEALLOCATE tc

Trim SQL Database Space

A modified version of a very useful script by Andrew Zavensky (zavensky@azdatabases.com) that shrinks overgrown log files in a database. Once it is complete, do a new database backup since the transaction logs are cleared out. Change the first line to the name of the database you want to shrink. The whole process should take well under a minute to run.

use my_database

-- CRW, 4/1/01 - Run this in the database whose log file you want to shrink.
-- This creates a stored procedure, calls it, and drops the stored procedure.
-- The whole process should take < 60 seconds.
-- Once complete, do a new set of database backups since the transaction log
-- is trashed.

if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
--------------------------------------------------------------------------------
-- Purpose: Shrink transaction log of the current database in SQL Server 7.0.
-- Author:  Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
--          zanevsky@azdatabases.com
--------------------------------------------------------------------------------
    @target_percent tinyint = 0,
    @target_size_MB int = 10,
    @max_iterations int = 1000,
    @backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on

declare @db         sysname,
        @last_row   int,
        @log_size   decimal(15,2),
        @unused1    decimal(15,2),
        @unused     decimal(15,2),
        @shrinkable decimal(15,2),
        @iteration  int,
	@file_max   int,
	@file	    int,
	@fileid     varchar(5)

select  @db = db_name(),
        @iteration = 0

create table #loginfo (
    id          int identity,
    FileId      int,
    FileSize    numeric(22,0),
    StartOffset numeric(22,0),
    FSeqNo      int,
    Status      int,
    Parity      smallint,
    CreateTime  numeric(22,0)
)

create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo, StartOffset )

create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from sysfiles where status & 0x40 = 0x40
select @file_max = @@rowcount

if object_id( 'table_to_force_shrink_log' ) is null
	exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )

insert  #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
select  @last_row = @@rowcount

select  @log_size = sum( FileSize ) / 1048576.00,
        @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
        @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
from    #loginfo

select  @unused1 = @unused -- save for later

select  'iteration'          = @iteration,
        'log size, MB'       = @log_size,
        'unused log, MB'     = @unused,
        'shrinkable log, MB' = @shrinkable,
        'shrinkable %'       = convert( decimal(6,2), @shrinkable * 100 / @log_size )

while @shrinkable * 100 / @log_size > @target_percent
  and @shrinkable > @target_size_MB
  and @iteration < @max_iterations begin
    select  @iteration = @iteration + 1 -- this is just a precaution

    exec( 'insert table_to_force_shrink_log select name from sysobjects
           delete table_to_force_shrink_log')

    select @file = 0
    while @file < @file_max begin
        select @file = @file + 1
        select @fileid = fileid from #logfiles where id = @file
        exec( 'dbcc shrinkfile( ' + @fileid + ' )' )
    end

    exec( 'backup log [' + @db + '] ' + @backup_log_opt )

    truncate table #loginfo
    insert  #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateTime ) exec ( 'dbcc loginfo' )
    select  @last_row = @@rowcount

    select  @log_size = sum( FileSize ) / 1048576.00,
            @unused = sum( case when Status = 0 then FileSize else 0 end ) / 1048576.00,
	    @shrinkable = sum( case when id < @last_row - 1 and Status = 0 then FileSize else 0 end ) / 1048576.00
    from    #loginfo

    select  'iteration'          = @iteration,
            'log size, MB'       = @log_size,
            'unused log, MB'     = @unused,
            'shrinkable log, MB' = @shrinkable,
            'shrinkable %'       = convert( decimal(6,2), @shrinkable * 100 / @log_size )
end

if @unused1 < @unused
select  'After ' + convert( varchar, @iteration ) +
        ' iterations the unused portion of the log has grown from ' +
        convert( varchar, @unused1 ) + ' MB to ' +
        convert( varchar, @unused ) + ' MB.'
union all
select	'Since the remaining unused portion is larger than 10 MB,' where @unused > 10
union all
select	'you may try running this procedure again with a higher number of iterations.' where @unused > 10
union all
select	'Sometimes the log would not shrink to a size smaller than several Megabytes.' where @unused <= 10

else
select  'It took ' + convert( varchar, @iteration ) +
        ' iterations to shrink the unused portion of the log from ' +
        convert( varchar, @unused1 ) + ' MB to ' +
        convert( varchar, @unused ) + ' MB'

exec( 'drop table table_to_force_shrink_log' )
go
sp_force_shrink_log
go
drop proc sp_force_shrink_log
go
dbcc loginfo
go