DatabaseJournal.com Staff

I’ve written stored procedures in the past where I could remember some of the code they contained, but not the name of the stored procedure. I developed this little stored procedure to help find those elusive stored procedures based on the code in the stored procedure. It searches the sysobjects and syscomments tables to find […]

This script will generate insert statements along with their data values. One can also specify a WHERE clause (giving it power and flexibility) or restrict the number of rows passing it numbers. Author: Raghu Pattath Download Script: sp_xfrdata_v2.0.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of […]

This script converts a fixed-length 14 character string from YYYYMMDDHHMMSS to datetime. Could be easily amended to accept different fixed-length strings as input. Params: fixed_str char(14), date_out datetime. Author: Paul Harris Download Script: sp_fixedstring_to_datetime.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in […]

A simple script to generate exec scripts that add users to databases and roles. Quite handy when restoring the same database from a different server. Author: Michael Leaman Download Script: roles.SQL Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, […]

One important responsiblity of the DBA is user management. When migrating a database from one server to another, the users should not be bothered with having to set up a new password for a new login. This is a script to create a stored procedure that will create a script that can be used to […]

This script will generate insert statements for the given tables. You can pass the tables names, separated by commas, into sp_DataAsInsCommand stored procedure as in the example below: EXEC sp_DataAsInsCommand ’employee,titleauthor,pub_info’ Updated on 5/25/01 to correct an issue with columns that are short like a column defined as char(1), to correct another issue with the […]

Find out where the particular column is referenced. Optionally, find if column has the particular value anywhere across database. Helps to get familiar with database and to check referential integrity. Run it: sp_ColumnHelp Column_Name [,Column_Value] Author: Michael Gladshteyn Download Script: sp_ColumnHelp.sql Disclaimer: We hope that the information on these script pages is valuable to you. […]

This code presents you with a nice list of object permissions for every object in a database. It seems to work fine under SQL 7.0, but I haven’t yet researched those system tables for any differences as I’m about 3:1 6.5 vs 7.0. Author: Wayne West Download Script: vsysShowPermissions.sql Disclaimer: We hope that the information […]

This very cool stored procedure will find all the stored procedures that match a certain criteria. This Stored procedure accepts an input parameter, this input parameter is searched in all sp and it prints the SP name , LineId , Text. Author: George A Theruviparambil Download Script: geoFindText Disclaimer: We hope that the information on […]

These script creates two procedures (usp_GetTableSizes and sp_SpaceUsed2) in the user database. The first procedure Usp_GetTableSizes reports each user table in the database along with it’s data size and the number of data rows it has. Usp_GetTableSizes uses the second store procedure sp_spaceused2 to collect metadata. Sp_spaceused2 is a slightly modified version of the system […]

Returns rowcounts for all tables in the current database, or if @tablename is provided, for that table only. Note : this procedure makes use of sysindexes, so make sure all indexes are up-to-date Author: Paul Silveira Download Script: ps_rowcount.txt Disclaimer: We hope that the information on these script pages is valuable to you. Your use […]

This script produces a script that drops and creates all triggers on a certain database. To use it create the procedure in the master db and save the result as *.sql. Author: Marino De Veirman Download Script: ScriptTriggers.sql Disclaimer: We hope that the information on these script pages is valuable to you. Your use of […]