VB and VBA Users Source Code: Useful system stored procedures
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Useful system stored procedures
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Thursday, April 19, 2001
Hits:
973
Category:
Database/SQL/ADO
Article:
The following are a list of useful system stored procedures: sp_helpconstraint: Returns a list of all constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines the constraint (for DEFAULT and CHECK constraints only): To get specific information about the constraints on a table use: sp_helpconstraint myTableName or to get summary information about the constaints on a database use: sp_helpconstraint sp_help: Reports information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type: This example lists information about each object in sysobjects USE master EXEC sp_help sysobjects sp_helpdb: Reports information about a specified database or all databases: To get specific information about a database use: sp_helpdb myDatabase or to get summary information about databases on the server use: sp_helpdb sp_who: Provides information about current users and processes. The information returned can be filtered to return only those processes that are not idle: eg. This example uses sp_who without parameters to report all current users: USE master EXEC sp_who Display all active processes: USE master EXEC sp_who 'active' set identity_insert: Allows explicit values to be inserted into the identity column of a table. i.e disables the IDENTITY for this table. SET IDENTITY_INSERT [database.[owner.]]{table} {ON | OFF} Note, at any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft SQL Server returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for. sp_spaceused: Displays the number of rows, disk space reserved, and disk space used by a table in the current database, or displays the disk space reserved and used by the entire database. eg. USE pubs EXEC sp_spaceused 'titles'
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
