SQL Server Scripts Scripts 7.0, 8.0, 9.0, 10.0, 10.50, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0 DBA Remote DBA Database Administration

calendar Aug 1, 2025 / · 4 min read · scripts sql  ·
Share on: linkedin copy

SQL Server Scripts and Database Commands 6.5, 7.0, 8.0, 9.0, 10.0, 10.50, 11.0, 12.0, 13.0, 14.0, 15.0, 16.0, 17.0

The corresponding version numbers and years: SQL Server 6.5 (1996), SQL Server 7.0 (1998), SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022, and SQL Server 2025

We are in the process of building a list of simple scripts used every day by DBA and others. If you have your own scripts you reuse in a text file on you workstation please send it to us.

To look at other scripts see the SQL Server Scripts Tag

Thanks for coming by!!

SQL Server Scripts

SqlServer70.com, Comcast Cellular Communications Inc (bought by SBC) were part of the early adopters program for SQL Server 7.0.  

Thanks For Coming By!!

Run Space Used command on tables

Run Space Used command on tables

1select 'sp\_spaceused ' + name + 'go' from sysobjects where type = 'U' order by name 

SP primary Keys on tables

1select 'select "Processing Table ' + name + '"go' + ' sp\_pkeys ' + name + 'go' from sysobjects where type = 'U' order by name

SQL Identity DBCC CHECKIDENT (jobs, NORESEED)

1select 'DBCC CHECKIDENT (' + name + ', NORESEED)' + ' go'
2from sysobjects where type = 'U' order by name

Update Statistics SQL

1select + "Update Statistics " + name + " go" + " SELECT getdate()" + " go" from sysobjects where type = 'u' order by name
2
3select 'Select \* From ' + name + 'go'  from sysobjects where type = 'U' order by name

Permissions At Object Level

 1select 'GRANT select ON ' + name + ' to SelectInsertUpdateDeleteExecSP' from sysobjects where type = 'u' order by name
 2GO
 3
 4select 'GRANT insert ON ' + name + ' to SelectInsertUpdateDeleteExecSP' from sysobjects where type = 'u' order by name  
 5GO
 6
 7select 'GRANT update ON ' + name + ' to SelectInsertUpdateDeleteExecSP' from sysobjects where type = 'u' order by name
 8GO
 9
10select 'GRANT delete ON ' + name + ' to SelectInsertUpdateDeleteExecSP' from sysobjects where type = 'u' order by name
11GO
12
13select 'GRANT exec ON ' + name + ' to SelectInsertUpdateDeleteExecSP' from sysobjects where type = 'p' order by name
14GO

Rebuild Clustered Indexes

1select distinct a.name from sysobjects a, sysindexes b where a.type = 'U' and a.id = b.id and b.indid = 1 order by a.name

Run DBCC SHOW CONTIG

1select 'DBCC SHOWCONTIG (' + CONVERT(varchar(12), id) + ')' + 'go' from sysobjects where type = 'U' order by name

Run Space Report

 1create table #tmp (server_name varchar(10),rundate datetime,database_name
 2
 3varchar(15), tbl_name varchar(30),total_rows int)
 4
 5SET NOCOUNT ON
 6
 7declare FKnames insensitive cursor for
 8
 9select name from sysobjects where type = 'U' order by name
10
11declare @FKname varchar(50)
12
13declare @TblName varchar(50)
14
15declare @i int
16
17declare @total_rows int
18
19declare @fordb varchar(15)
20
21select @fordb = db_name()
22
23select @i = 1
24
25open FKnames
26
27fetch FKnames into @TblName
28
29While (@@fetch_status = 0)
30
31begin
32
33select @total_rows = rows FROM sysindexes WHERE id = object_id (@TblName)
34
35AND indid < 2
36
37insert into #tmp values (@@servername,getdate(),@fordb, @TblName,
38
39@total_rows)
40
41/* SELECT SUBSTRING(@@servername,1,15), getdate(), CONVERT(CHAR(5),@i),
42
43@TblName, rows FROM sysindexes
44
45WHERE id = object_id (@TblName) AND indid < 2 */
46
47select @i = @i + 1
48
49fetch FKnames into @TblName
50
51end
52
53deallocate fknames
54
55SET NOCOUNT OFF
56
57select server_name, rundate,database_name,tbl_name,total_rows from #tmp
58
59order by total_rows desc
60
61drop table #tmp

DBCC Reindex Script

1select "DBCC DBREINDEX ( " + name + ", '', 0, SORTED_DATA_REORG)" + "go" from sysobjects where type = 'u' order by name 
2
3select "DBCC DBREINDEX ( " +name + ", '', 0, SORTED_DATA_REORG)" + "go" + "SELECT getdate()" + "go" from sysobjects where type = 'u' order by name
4
5----
6
7select "DBCC DBREINDEX ( " + name + ", '', 0, SORTED_DATA_REORG)" + "go" + "Print ' " + name + "'" + "TIME START/END = " + CONVERT(char(30), GETDATE()) + "go" from sysobjects where type = 'u' order by name

DBCC ReIndex Script with getdate's

1select "DBCC DBREINDEX ( " + name + ", '', 0, SORTED_DATA_REORG)" + "go" from sysobjects where type = 'u' order by name 
2
3select "DBCC DBREINDEX ( " +name + ", '', 0, SORTED_DATA_REORG)" + "go" + "SELECT getdate()" + "go" from sysobjects where type = 'u' order by name
4
5----
6
7select "DBCC DBREINDEX ( " + name + ", '', 0, SORTED_DATA_REORG)" + "go" + "Print ' " + name + "'" + "TIME START/END = " + CONVERT(char(30), GETDATE()) + "go" from sysobjects where type = 'u' order by name

Posts in this series