Oracle 10g
Solaris 10
SQL Server 2000
New Zealand
Lacrosse
Junk Mail
Books Used
SQL Server Scripts
SqlServer70.com, 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!!
- 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 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())