SQL Server Scripts (SQL Server 2005)

Harman Research Inc , Comcast Cellular Communications Inc (bought by SBC) were part of the early adopters program for SQL Server 7.0.   Also, feel free to check out our Solaris UNIX 8, 9, 10 page or the  Oracle 8i, 9i, 10g page. You can check out the the Books/Manuals that we use Regularly.  

Thanks For Coming By!!

• Run Space Used command on tables
• SP primary Keys on tables
• SQL 7.0 Identity DBCC CHECKIDENT (jobs, NORESEED)
• Update Statistics SQL
• Permissions At Object Level
• Rebuild Clustered Indexes
• Run DBCC SHOW CONTIG
• Run Space Report
• DBCC Reindex Script
• DBCC ReIndex Script with getdate's

 

 

Run Space Used command on tables

select 'sp_spaceused ' + name + 'go' from sysobjects where type = 'U' order by name 

SP primary Keys on tables

select 'select "Processing Table ' + name + '"go' + ' sp_pkeys ' + name + 'go' from sysobjects where type = 'U' order by name

SQL 7.0 Identity DBCC CHECKIDENT (jobs, NORESEED)

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

Update Statistics SQL

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

Permissions At Object Level

select 'GRANT select ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'u' order by namegoselect 'GRANT insert ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'u' order by namegoselect 'GRANT update ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'u' order by namegoselect 'GRANT delete ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'u' order by nameselect 'GRANT exec ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'p' order by name

Rebuild Clustered Indexes

select 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

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

Run Space Report

create table #tmp (server_name varchar(10),rundate datetime,database_namevarchar(15), tbl_name varchar(30),total_rows int)SET NOCOUNT ONdeclare FKnames insensitive cursor forselect name from sysobjects where type = 'U' order by namedeclare @FKname varchar(50)declare @TblName varchar(50)declare @i intdeclare @total_rows intdeclare @fordb varchar(15)select @fordb = db_name()select @i = 1open FKnamesfetch FKnames into @TblNameWhile (@@fetch_status = 0)beginselect @total_rows = rows FROM sysindexes WHERE id = object_id (@TblName)AND indid < 2insert into #tmp values (@@servername,getdate(),@fordb, @TblName,@total_rows)/* SELECT SUBSTRING(@@servername,1,15), getdate(), CONVERT(CHAR(5),@i),@TblName, rows FROM sysindexesWHERE id = object_id (@TblName) AND indid < 2 */select @i = @i + 1fetch FKnames into @TblNameenddeallocate fknamesSET NOCOUNT OFFselect server_name, rundate,database_name,tbl_name,total_rows from #tmporder by total_rows descdrop table #tmp

DBCC Reindex Script

select "DBCC DBREINDEX ( " + name + ", '', 0, SORTED_DATA_REORG)" + "go" from sysobjects where type = 'u' order by name select "DBCC DBREINDEX ( " +name + ", '', 0, SORTED_DATA_REORG)" + "go" + "SELECT getdate()" + "go" from sysobjects where type = 'u' order by name----select "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

Print "Print 'Start Of Rebuild Indexes For Database"goPrint "go"goPrint "Select getdate()"goPrint "go"goselect "DBCC DBREINDEX ( " +name + ", '', 0, SORTED_DATA_REORG)" + "go" +"Print ' '" +"Print 'Sucessfully Rebuilt Indexes For Table " + name + "'" + "go" + "Select getdate()" + "go" from sysobjects where type = 'u' order by namegoPrint " " Print "Print 'END Of Rebuild Indexes For Database"goPrint "go"goPrint "Select getdate()"

go

 

---select "Table " + name + " TIME START/END = " + CONVERT(char(30), GETDATE()) from sysobjects where type = 'u' order by nameselect CONVERT(char(30), GETDATE())