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 (https://blog.sqlauthority.com) , BOL – sys.objects

, , ,
Previous Post
SQL SERVER – 2005 – Retrieve Processes Using Specified Database
Next Post
SQLAuthority News – Download White Papers – Migration from MySQL, Oracle, Sybase, or Microsoft Access to Microsoft SQL Server

Related Posts

18 Comments. Leave new

  • Wonderful article. Keep it up man.

    You are blogging’s tendulkar.

    Rana

    Reply
  • mayank tripathi
    April 7, 2008 10:50 pm

    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.

    Reply
  • 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

    Reply
  • When i run above query. I got following error.

    Invalid object name ‘sys.objects’.

    Reply
    • The query works from version 2005 onwards only

      Reply
      • Sir am using sql server 2005 only but am also facing the same issue . Invalid object name ‘sys.objects’..

      • Madhi Sir, Many functions like rank() ,(Views like sys.objects )also not found in our sql server 2005 . I found in one forum one person has answered as “System Default Catalog may be dropped ” . Is it possible … if so how shall i retain it..

  • 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)

    Reply
  • 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, ……..)

    Reply
  • 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

    Reply
  • Vijay M ahire
    April 17, 2009 6:39 pm

    Can anybody tell me limitations of Rule in sql2005 over
    SQL CLR ?

    Reply
  • Hi,

    Can any body tell about performance with user define data type and rule.
    and compare it with check constraint.

    Thanks

    Reply
  • is there any option to view all objects at a server level ( including all databases in server)?

    Reply
  • Where can I find the definition of CLR udf (type = ‘FT’). Using sql 2008 r2

    Reply
  • Ananth Sudhakar
    June 25, 2013 3:38 pm

    my goal is to create the table in X schema and need to access this same table is Y schema.

    Reply
    • Ananth Sudhakar
      June 25, 2013 3:40 pm

      I did the same functionality in oracle using ‘public synoymn’.

      Is there any other way we can achieve this functionality.. it would be really helpful…

      Reply

Leave a Reply Cancel reply

Menu
Exit mobile version