Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Generate A List Of Object Types By Using OBJECTPROPERTY
From Wiki
How do you query the sysobjects system table and get the object type back for every single object You can use the type and xtype columns, these contain the following data
xtype
Object type. Can be one of these object types:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
type
Object type. Can be one of these values:
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
FN = Scalar function
IF = Inlined table-function
K = PRIMARY KEY or UNIQUE constraint
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
V = View
X = Extended stored procedure
Or you can use OBJECTPROPERTY. OBJECTPROPERTY is better in my opinion because you can see right away what you are looking for For example OBJECTPROPERTY ( id , 'IsUserTable' ) is much easier to understand than type = 'u'
Bu using CASE with OBJECTPROPERTY we can generate a nice report
- SELECT name,CASE
- WHEN OBJECTPROPERTY ( id , 'IsSystemTable' ) =1 THEN 'System Table'
- WHEN OBJECTPROPERTY ( id , 'IsProcedure' ) =1 THEN 'Procedure'
- WHEN OBJECTPROPERTY ( id , 'IsPrimaryKey' ) =1 THEN 'Primary Key'
- WHEN OBJECTPROPERTY ( id , 'IsDefault' ) =1 THEN 'Default'
- WHEN OBJECTPROPERTY ( id , 'IsForeignKey' ) =1 THEN 'Foreign Key'
- WHEN OBJECTPROPERTY ( id , 'IsCheckCnst' ) =1 THEN 'Check Constraint'
- WHEN OBJECTPROPERTY ( id , 'IsView' ) =1 THEN 'View'
- WHEN OBJECTPROPERTY ( id , 'IsConstraint' ) =1 THEN 'Constraint'
- WHEN OBJECTPROPERTY ( id , 'IsTrigger' ) =1 THEN 'Trigger'
- WHEN OBJECTPROPERTY ( id , 'IsScalarFunction' ) =1 THEN 'Scalar Function'
- WHEN OBJECTPROPERTY ( id , 'IsTableFunction' ) =1 THEN 'Table Valued Function'
- WHEN OBJECTPROPERTY ( id , 'IsRule' ) =1 THEN 'Rule'
- WHEN OBJECTPROPERTY ( id , 'IsExtendedProc' ) =1 THEN 'Extended Stored Procedure'
- WHEN OBJECTPROPERTY ( id , 'IsUserTable' ) =1 THEN 'User Table'
- END ObjectType, *
- FROM sysobjects
And of course there are a bunch of INFORMATION_SCHEMA views that you can use to get some of the same information back
- SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
- SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
- SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- SELECT * FROM INFORMATION_SCHEMA.TABLES
- SELECT * FROM INFORMATION_SCHEMA.VIEWS
- SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Contributed by: --SQLDenis 16:38, 6 June 2008 (GMT)
Part of SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.