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: *
 


Advertisements

MySQL HOSTING
MySQL hosting provider, clustering and replication supported. DBA Services Included.

SQL SERVER HOSTING
Reliable, powerful SQL Server 2008 hosting with ASP.NET on Windows 2008 Servers.

SQL Server DBA

I am a SQL Server DBA with almost 9 years of experience in database technologies.

 I am again in process of redesigning this website using ASP.NET 2.0 and AJAX. The main purpose of new SQL DBA website is to offer more features and make it easy for me to update contents on regular basis.