Recent site activity

Conatct Me

Pre Written Sql Script

DataAsInsCommand
Version: SQL Server 7.0/2000

This script will generate insert statements for the given tables. You can pass
the tables names into DataAsInsCommand stored procedure separated by commas,
as in the example below:

EXEC DataAsInsCommand 'employee,titleauthor,pub_info'


ExportToExcel
Version: SQL Server 7.0/2000

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'


 

replace_substring
Version: SQL Server 7.0/2000

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

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

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

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

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

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

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

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

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

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

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

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'


ExportToExcel
Version: SQL Server 6.5

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

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

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

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

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

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

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

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

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

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

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

You can defragment all the indexes on all the tables in your database periodically
(for example, one time per week at Sunday) to reduce fragmentation. The DBCC
INDEXDEFRAG statement cannot automatically defragment all indexes on all the
tables in a database; it can only work on one table and one index at a time.
You can use this script to defragment all indexes in every table in the current
database.


RebuildAllIndexes
Version: SQL Server 7.0/2000

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


CheckAllTables
Version: SQL Server 7.0/2000

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 7.0/2000

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 ('?')"


sp_lock2
Version: SQL Server 7.0/2000

This stored procedure can be used instead of sp_lock system stored procedure to
return more detailed locking view (it can return user name, host name, database
name, object name, index name and object owner).
This is the example to use sp_lock2:

EXEC sp_lock2
ExportToWord
Version: SQL Server 7.0/2000

This stored procedure can be used to export the text string into Microsoft Word.
You can pass the text string and the file name (if the file name was not
specified, the c:\ImportToWord.doc will be used) into this stored procedure,
as in the example below:

EXEC ExportToWord @Text = 'Test example',
                  @filename = 'c:\ImportToWord.doc'
Comments