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
18 Comments. Leave new
Wonderful article. Keep it up man.
You are blogging’s tendulkar.
Rana
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.
select * from dbo.authors where '%,' @String+',%' like '%,' +state ',%'
My previous reply should be
select * from dbo.authors where '%,'+@String+',%' like '%,'+state +',%'
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
When i run above query. I got following error.
Invalid object name ‘sys.objects’.
The query works from version 2005 onwards only
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)
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, ……..)
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
Can anybody tell me limitations of Rule in sql2005 over
SQL CLR ?
Hi,
Can any body tell about performance with user define data type and rule.
and compare it with check constraint.
Thanks
is there any option to view all objects at a server level ( including all databases in server)?
Where can I find the definition of CLR udf (type = ‘FT’). Using sql 2008 r2
my goal is to create the table in X schema and need to access this same table is Y schema.
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…