asked
Query to get list of objects created under a database
Hi, Any query to get list of objects created under a database. Tnx.
Query [sys.objects][1] (sysobjects SQL Server 2000), it does not list you triggers. [1]:
http://msdn.microsoft.com/en-us/library/ms190324.aspx
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
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
