find list all user tables objects in sql server

If you want to find all the user tables, stored procedures or other user objects in SQL Server then "sysobjects" table / view is the place to look. There are five main columns name, id, type or xtype, parent_obj and crdate. The type column defines the type of object.

U = User Table
P = Stored Procedure
check complete list of possible values in books online.

To get the list of all user objects, I will be using ObjectProperty (OBJECT ID, 'IsMSShipped') to check if the object was created by Microsoft.

SELECT [name], id, type, OBJECT_NAME(parent_obj) AS Parent_Object, crdate FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0

Above query will return all the user objects in current database including Tables, Stored Procedures, Views, Primary Keys etc. The parent_obj is the ID of parent object incase it’s a primary key or unique key etc. I am using OBJECT_NAME built-in function to get the name of parent object.

SELECT [name], id, type, object_name(parent_obj) AS Parent_Object, crdate FROM sysobjects
WHERE type = 'U'
AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0

The second query will return only user tables in current database, because I am filtering the records by specifying type = 'U'

SELECT [name], id, type, object_name(parent_obj) AS Parent_Object, crdate FROM sysobjects
WHERE type = 'P'
AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0

Now I have changed the type to 'P' to list only stored procedures. You can also list more then one type of objects with single query.

SELECT [name], id, type, object_name(parent_obj) AS Parent_Object, crdate FROM sysobjects
WHERE [type] IN ('U','P')
AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0

You can also reverse it to exclude the type of objects you don’t want to view.

SELECT [name], id, type, object_name(parent_obj) AS Parent_Object, crdate FROM sysobjects
WHERE [type] NOT IN ('U','P')
AND
OBJECTPROPERTY(id, 'IsMSShipped') = 0

I guess the above queries will give you enough ideas to find or get the list of objects you are trying to find in your SQL Server database.

 



 4/15/2008


Add Comments
Name: *
Email: *
URL:
Comments: *