SQL Server 6.5 Scripts

ExportToExcel
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to insert the result set of the particular
select statement into Excel file (c:\ImportToExcel.xls, by default).
You can pass the server name, user name, user password, the select statement
to execute, and the file name to store the results set, as in the example below:

EXEC ExportToExcel @server = '.',
                   @uname = 'sa',
                   @QueryText = 'SELECT au_fname FROM pubs..authors',
                   @filename = 'c:\ImportToExcel.xls'


GetSPTrigValues
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to return the names of each view, rule,
default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure,
which contain the particular text value. You should pass the database name
and the text value to search, as in the example below:

EXEC GetSPTrigValues @dbname = 'pubs', @txtvalue = 'sales'


GetAlltblRowsSize
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to get the size of all user tables in the
particular database with the number of their rows. You should simply pass the
database name, as in the example below (if the database name was not specified,
the current database will be used):

EXEC GetAlltblRowsSize 'pubs'


RebuildAllIndexes
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to rebuild all indexes in the particular
database. You can pass the database name and the fillfactor value into
RebuildAllIndexes stored procedure, as in the example below
(if the database name was not specified, the current database will be used,
if the fillfactor was not specified, the default fillfactor value will be used):

EXEC RebuildAllIndexes 'pubs', 70


GetTbColList
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to return all tables, which contain the
particular column. You should pass the database name and the column name,
as in the example below (if the database name was not specified, the current
database will be used):

EXEC GetTbColList @dbname = 'pubs', @colname = 'au_id'


GetTbColValues
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to return all tables and table's columns,
which contain the particular text value. The GetTbColValues stored procedure
will scan all tables in the current database to search the particular text
value in the char, varchar and text columns.
You should pass the text value to search, as in the example below:

EXEC GetTbColValues 'John'


SetTbColValues
Version: SQL Server 6.5
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to search and replace text in the char,
nchar, varchar and nvarchar columns in all tables in the current database.
You should pass the text value to search and the text value to replace.
So, to replace all char, nchar, varchar and nvarchar columns which contain
the value 'John' with the value 'Bill', you can use the following:

EXEC SetTbColValues @search_value = 'John',
                    @replace_value = 'Bill'


CheckAllTables
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to check the integrity of the data and index
pages for all user tables in the particular database. In comparison with DBCC
CHECKDB statement, this stored procedure takes less time to run, because only
user tables will be checked. You can pass the database name into CheckAllTables
stored procedure, as in the example below (if the database name was not specified,
the current database will be used):

EXEC CheckAllTables 'pubs'


ForEachUserTable
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to run some work for each user tables in the
particular database.
You can pass the database name and the string to execute into ForEachUserTable
stored procedure, as in the example below (if the database name was not specified,
the current database will be used):

EXEC ForEachUserTable @dbname = 'pubs',
                      @str = "DBCC CHECKTABLE ('?')"


GetObjNameList
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure can be used to return the names of the tables, constraints,
stored procedures, views, rules and triggers, which contain the particular value.
You should pass the database name and the value to search, as in the example below
(if the database name was not specified, the current database will be used):

EXEC GetObjNameList @dbname = 'pubs', @text = 'author'


GetObjCreateDate
Version: SQL Server 6.5
Created by: Alexander Chigrik

This stored procedure will scan a database for objects created within the
StartDate and EndDate and return their names and creation date.
You should pass the database name, the StartDate and the EndDate as in the
example below (if the database name was not specified, the current database
will be used, if the @StartDate or @EndDate was not specified, the current
date will be used):

EXEC GetObjCreateDate 'pubs', '01/05/2002', '06/05/2002'