T-SQL Scripts

replace_substring
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik

This stored procedure can be used to search and replace substring 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
substring 'John' with the substring 'Bill', you can use the following (in
comparison with the SetTbColValues stored procedure, this stored procedure
replace only substring, not the entire column's value):

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


ChangeAllObjOwner
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik

This stored procedure can be used to run through all of a specific database's
objects owned by the 'oldowner' and change the old owner with the new one.
You should pass the old owner name and the new owner name, as in the example below:

EXEC ChangeAllObjOwner @oldowner = 'John', @newowner = 'Alex'


GetObjCreatedBeforeDate
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik

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

EXEC GetObjCreatedBeforeDate 'pubs', '01/05/1999'


SetTbColValues
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik

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'


GenerateScript
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik

This script will generate script for all tables and all dependent objects for
the given database.
You can pass the Server name, user name, user password, database name and
file name into GenerateScript stored procedure, as in the example below:

EXEC GenerateScript @server = 'Server_Name',
                    @uname = 'User_Name', 
                    @pwd = 'Password',
                    @dbname = 'Database_Name', 
                    @filename = 'c:\File_Name.sql'


GetTbColList
Version: SQL Server 7.0/2000
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 7.0/2000
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,
nchar, varchar, nvarchar, text, and ntext columns. You should pass the text value
to search, as in the example below:

EXEC GetTbColValues 'John'


GetSPTrigValues
Version: SQL Server 7.0/2000
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 7.0/2000
Created by: Alexander Chigrik

This stored procedure can be used to get the size of all users 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'


GetObjNameList
Version: SQL Server 7.0/2000
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 7.0/2000
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'


GenerateSP
Version: SQL Server 7.0/2000
Created by: Alexander Chigrik

This script will generate script for all stored procedures for the given database.
You can pass the Server name, user name, user password, database name and file name
into GenerateSP stored procedure, as in the example below:

EXEC GenerateSP @server = 'Server_Name',
                       @uname = 'User_Name',
                       @pwd = 'Password',
                       @dbname = 'Database_Name',
                       @filename = 'c:\script.sql'