question

pvsrinivasrao avatar image
pvsrinivasrao asked

Query to get list of objects created under a database

Hi, Any query to get list of objects created under a database. Tnx.
sql-server-2008t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
Query [sys.objects][1] (sysobjects SQL Server 2000), it does not list you triggers. [1]: http://msdn.microsoft.com/en-us/library/ms190324.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ah, you where 14sec ahead of me. :)
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
SELECT SCHEMA_NAME(o.[schema_id]), * FROM sys.objects o --WHERE type 'S' --remove the comment mark if you like to remove system tables from the result. Will give you the following object types: AF = Aggregate function (CLR) C = CHECK constraint D = DEFAULT (constraint or stand-alone) F = FOREIGN KEY constraint FN = SQL scalar function FS = Assembly (CLR) scalar-function FT = Assembly (CLR) table-valued function IF = SQL inline table-valued function IT = Internal table P = SQL Stored Procedure PC = Assembly (CLR) stored-procedure PG = Plan guide PK = PRIMARY KEY constraint R = Rule (old-style, stand-alone) RF = Replication-filter-procedure S = System base table SN = Synonym SQ = Service queue TA = Assembly (CLR) DML trigger TF = SQL table-valued-function TR = SQL DML trigger U = Table (user-defined) UQ = UNIQUE constraint V = View X = Extended stored procedure
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

but i couldnt see how to filter results based on DB name.
0 Likes 0 ·
You have to use this query in each db you would like to get a list from. If you like to get all objects from all databases you could try the undocumented/unsupported procedure sp_for_eachdb
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.