


This page contains some useful scripts for Microsoft SQL Server. These are tested on SQL Server 2000, but should run on later versions of SQL Server with little or no modification. As ever, these have no warranty whatsoever. Use at your own risk.
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]
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
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
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 set of database backups 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
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
@mydbname nvarchar(128),
--------------------------------------------------------------------------------
-- 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),
@sql nvarchar(65)
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)
)
set @sql = 'use ' + @mydbname
EXECUTE sp_executesql @sql
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
DECLARE mydb CURSOR READ_ONLY FOR
select name as mydbname from master.dbo.sysdatabases where mode = 0
DECLARE @mydbname AS VarChar(64)
DECLARE @sql AS nVarChar(64)
OPEN mydb
FETCH NEXT FROM mydb INTO @mydbname
WHILE @@FETCH_STATUS = 0
BEGIN
print @mydbname
set @sql = 'use master ; EXECUTE sp_force_shrink_log ' + @mydbname
EXECUTE sp_executesql @sql
-- EXECUTE sp_force_shrink_log
FETCH NEXT FROM mydb INTO @mydbname
END
CLOSE mydb
DEALLOCATE mydb
go
use master
go
drop proc sp_force_shrink_log
go
dbcc loginfo
go
