|
Finding Useful Database Information in SQL Server (SQL Server) |
|
|
Written by: Andy Sun
Finding Useful Database Information in SQL ServerIn SQL Server, the sysobjects table contains one row for each object created within a database. It has a row for every constraint, default, log, rule, stored procedure and view in the database. A user can use this table to retrieve information about the database. The xtype column in the sysobjects table can be used to get useful database information. This column specifies the type for the row entry in sysobjects.
For example, in order to find all user tables in a database, we can run query like this
“Select * from sysobjects where xtype=’U’ “. This will give you a list of all user defined tables. Or you can use this query “select * from sysobjects where xtype = ‘P’ and category = ‘0’ “, it will give you a list of all user defined stored procedures.
The following is a the list of all possible values for xtype column
• C=Check constraint
• D=Default or Default constraint
• F=Foreign Key constraint
• L=log
• FN=Scalar Function
• IF=Inline Table Function
• P=Stored Procedure
• PK=Primary Key
• RF=Replication filter stored procedure
• S=System Table
• TF=Table Function
• TR=Trigger
• U=User Table
• UQ=Unique constraint
• V=View
• X=Extended Stored Procedure
|
|
|
|
|
|