SQL Server Scripts

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 page or the  Oracle 8i page.  

Thanks For Coming By!

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 name

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

go

select 'GRANT insert ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'u' order by name

go

select 'GRANT update ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'u' order by name

go

select 'GRANT delete ON ' + name + ' to SelectInsertUpdateDeleteExecSP' + 'go' from sysobjects where type = 'u' order by name

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

varchar(15), tbl_name varchar(30),total_rows int)

SET NOCOUNT ON

declare FKnames insensitive cursor for

select name from sysobjects where type = 'U' order by name

declare @FKname varchar(50)

declare @TblName varchar(50)

declare @i int

declare @total_rows int

declare @fordb varchar(15)

select @fordb = db_name()

select @i = 1

open FKnames

fetch FKnames into @TblName

While (@@fetch_status = 0)

begin

select @total_rows = rows FROM sysindexes WHERE id = object_id (@TblName)

AND indid < 2

insert into #tmp values (@@servername,getdate(),@fordb, @TblName,

@total_rows)

/* SELECT SUBSTRING(@@servername,1,15), getdate(), CONVERT(CHAR(5),@i),

@TblName, rows FROM sysindexes

WHERE id = object_id (@TblName) AND indid < 2 */

select @i = @i + 1

fetch FKnames into @TblName

end

deallocate fknames

SET NOCOUNT OFF

select server_name, rundate,database_name,tbl_name,total_rows from #tmp

order by total_rows desc

drop 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"

go

Print "go"

go

Print "Select getdate()"

go

Print "go"

go

select "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 name

go

Print " " Print "Print 'END Of Rebuild Indexes For Database"

go

Print "go"

go

Print "Select getdate()"

go

---

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

select CONVERT(char(30), GETDATE())

[ Oracle 8i ] [ Solaris UNIX ] [ Books We Use ] [ Stop Junk Mail ] [ Lacrosse ] [ New Zealand ]

Send mail to webmaster@harmanresearch.com with questions or comments about this web site.
Copyright © 1998-2001 Harman Research Inc
Last modified: February 25, 2002