DatabaseJournal.com Staff

This stored procedure for SQL Server 7.0 and 2000 will return a user’s permissions for a database object. It contains two null-able parameters: @user_name and @object_name. The parameter @user_name will accept a SQL Role name or a User name. The parameter @object_name will accept any type of database object name. The result set will return […]

Here is a small script that will perform maintenance on all databases on a SQL server. The script looks at what databases are configured on the SQL Server then retrieves a list of tables for each DB on the SQL Server. The script then uses this list to perform DBCC DBREINDEX on each table in […]

Here’s a script that will find a value in a field. The stored procedure will go through each field in each table of a database and look for a specified value (for example, order number ‘1-ASD-DER-45T’ or similar). The value you are looking for and the data type (varchar, int, etc.) need to be supplied […]

This script creates an sp that can be used to search the SQL Error Log for a user specified search criteria. This can be very handy when trying to find a particular problem in very large SQL Error Logs. The procedure Reads the registry to determine where Your SQL install exists so that you do […]

I use the sp_LPAD stored procedure wherever I need to generate a padded string, like a bank account or serial number. It’s a bit like the Oracle built-in function of the same name, and can be called like this: EXEC sp_LPAD @OrderRef OUTPUT Obviously, you’ll need to CAST or CONVERT the parameter if it is […]

This simple script takes a table name as input and then displays the structure of the table in a neat and more readable format for the user. The script was created in and tested on SQL Server 2000. Author: Senthil Kumar Download Script: ScriptTblStruct.txt Disclaimer: We hope that the information on these script pages is […]

Here is a simple stored procedure that allows you to drop all user-created indexes for a given table. Sp_ Dropindexes takes a single parameter (@objname) and drops all of its user-created indexes. The procedue was created in and tested on SQL Server 2000. Author: Sachin Wable Download Script: sp_dropindex.sql Disclaimer: We hope that the information […]

Using Index Server’s Objects Did you know that you can use the Index Server, the content indexing engine included with IIS4, as you would any other database? You can make ADO calls to it and search your site, providing you with an excellent search engine for your webs. It’s pretty simple, really. You just declare […]

When creating new database objects or altering old database objects, the one thing that is often overlooked is the object’s permissions. SQL Server Enterprise Manager does not make granting all the permissions you need very easy. In databases with hundreds to thousands of objects, this can be a very time consuming task. This stored procedure […]

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided “as -is”, without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title […]

In a heavily normalized database, it is often difficult to modify/drop tables when lots of foreign keys exist. This script will list all of the foreign keys in the database that reference a given table name. It will also generate scripts to drop the foreign keys to make dropping the parent table easier. Supply the […]

In a heavily normalized database, it is often difficult to modify/drop tables when lots of foreign keys exist. This script will list all of the foreign keys in the database that reference a given table name. It will also generate scripts to drop the foreign keys to make dropping the parent table easier. Supply the […]