SQL Server Scripts, SQL Server DBA, Remote Services, Database Administration Remote SQL Server support

calendar Nov 8, 2023 · 4 min read · SQL Server Scripts SQL  ·
Share on: linkedin copy

SQL Server Scripts (SQL Server 2012)

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!!



SQL Server Script

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 Server Identity DBCC CHECKIDENT jobs NORESEED

1select 'DBCC CHECKIDENT (' + name + ', NORESEED)' + ' go'from 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 nameselect 'Select * From ' + name + 'go'  from sysobjects where type = 'U' order by name

Permissions At Object Level

1select '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

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_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

1select "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 getdates

1Print "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()"
2
3go
4
5\---select "Table " + name + " TIME START/END = " + CONVERT(char(30), GETDATE()) from sysobjects where type = 'u' order by nameselect CONVERT(char(30), GETDATE())

Posts in this Series