SQL SERVER – 2005 – Retrieve Any User Defined Object Details Using sys.objects Database

sys.objects object catalog view contains a row for each user-defined, schema-scoped object that is created within a database. You can retrieve any user defined object details by querying sys.objects database.

Let us see one example of sys.objects database usage. You can run following query to retrieve all the information regarding name of foreign key, name of the table it FK belongs and the schema owner name of table.
USE AdventureWorks;
GO
SELECT name AS ObjectName,
OBJECT_NAME(schema_id) SchemaName,
OBJECT_NAME(parent_object_id) ParentObjectName, name, *
FROM sys.objects
WHERE type = 'F'
GO

You can use any of the following in your WHERE clause and retrieve necessary information.

Object type:

AF = Aggregate function (CLR)
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
PK = PRIMARY KEY constraint
P = SQL stored procedure
PC = Assembly (CLR) stored procedure
FN = SQL scalar function
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TR = SQL DML trigger
IF = SQL inline table-valued function
TF = SQL table-valued-function
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
IT = Internal table

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL – sys.objects

About these ads

19 thoughts on “SQL SERVER – 2005 – Retrieve Any User Defined Object Details Using sys.objects Database

  1. Can u plz solve the below mentioned query.

    use pubs
    declare @String varchar(100)
    set @String = ”’CA”,”KS”,”TN”,”OR”’

    print @String

    select * from dbo.authors where state in (@String )

    The select statement is no displaying data. Where as when we print the value of @String, it willcome correct, but with select statement it is not working.
    Plz resolve this for me.

    Like

  2. hello
    here the select statement should be executed along with the other statments.because @string is defined within that t-sql batch.

    select * from dbo.authors where state in (‘CA’,’OR’,’TN’)
    this should be the form.ca ,or and tn should be in single quotes.

    i had one solution for ur problem.
    select * from dbo.authors where state in (select distinct(state) from dbo.authors )
    select * from dbo.authors where state in (‘CA’,’OR’,’TN’)

    i hope this will help u.if got the best solution for ur query i will reply u.

    have a nice day.
    dayanand.chilakalapudi

    Like

  3. Hi ,
    this is nagaraju. i have a smal doubt.
    How to retrieve the unique dataitems from database randomly using sqlserver2005.
    (ie.,item should not be repeated again)

    Like

  4. Hi Pinal Dave and others.
    My question concerns both 2000 & 2005.
    I need to retrieve the (Create)scripts for all my user defined objects in the database (Tables, Views, Procedures, Functions etc).
    Currently I can only obtain scripts for my User Defined Tables
    By retrieving the column names/types/keys etc and using this data have my application re-generate the scripts.
    Is it possible to retrieve these scripts from SQLServer2000/2005 through a query if so how.

    Eg:
    –Query
    SELECT * FROM [Some built in function] (‘MyDbo’)
    –Result (say MyDbo is a table)
    CREATE TABLE [MyDbo]([ID] INT NOT NULL IDENTITY, ……..)

    Like

  5. Hi Pinal,

    There is aminor correction in your code…According to me right code should be like this…..

    USE AdventureWorks;
    GO
    SELECT name AS ObjectName,
    SCHEMA_NAME(schema_id) SchemaName,
    OBJECT_NAME(parent_object_id) ParentObjectName, name, *
    FROM sys.objects
    WHERE type = ‘F’
    GO

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s