SQL Server Help: Useful System Stored Procedures
SQL Server provides a number of useful system stored procedures that allow you to determine information about the database itself. Note that if you have problems running any of these stored procedures, check that the user you're running them as actually has permissions to execute the stored procedure in question.
Find out which tables and views are in a databaseThis is simply achieved by making use of the sp_tables system stored procedure, i.e.
EXEC sp_tables
The returned results set contains a list of the tables (and views) in the current database. The TABLE_NAME column gives the name of the table. The TABLE_TYPE column indicates if the table is a TABLE, SYSTEM TABLE or a VIEW. The TABLE_OWNER column is also useful as it shows the table's owner.
Find out which columns are in a database tableThis is achieved by making use of the sp_columns system stored procedure, passing in the table name as the parameter, i.e.
EXEC sp_columns 'sales'
The returned results set contains a list of the table's columns. There are quite a few columns in the results set, so only the most useful will be described here:
The COLUMN_NAME column gives the name of the column.
The TYPE_NAME column gives the column's data type.
The LENGTH column gives the column's data type length.
The IS_NULLABLE column shows whether the column accepts null values.
Programmatically display a View's Transact-SQLThe system stored procedure sp_helptext will return a results set containing the lines of Transact-SQL that comprise the View. For example, the following will return the text of the stored procedure Order Details Extended's CREATE VIEW statement:
EXEC sp_helptext 'Order Details Extended'
The text of the CREATE VIEW statement is contained within the Text column of the results set.
Find out which stored procedures are in a databaseThis is achieved by making use of the sp_stored_procedures system stored procedure,
EXEC sp_stored_procedures
The returned results set contains a list of the stored procedures in the database in which it is executed. in the results set, the PROCEDURE_NAME column gives the name of the stored procedure.
Programmatically display a stored procedure's Transact-SQLThe system stored procedure sp_helptext will return a results set containing the lines of Transact-SQL that comprise the stored procedure. For example, the following will return the text of the stored procedure CustOrdersDetail's CREATE PROCEDURE statement:
EXEC sp_helptext 'CustOrdersDetail'
The text is contained within the Text column of the results set.
Useful LinksPuneet Singla
Posts
