Thursday 18 August 2016

SQL Server - Get DB Object Names, Count

FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'

The above example lists all the tables in the database.
You can modify the query to access other details about the db objects.
Also the same can be used to count the number of objects (tables / stored procedures) in the database.

Below are the reference to the other XTYPE values corresponding to each objects.

-- AF: Aggregate function (CLR)
-- C: CHECK constraint
-- D: Default or DEFAULT constraint
-- F: FOREIGN KEY constraint
-- L: Log
-- FN: Scalar function
-- FS: Assembly (CLR) scalar-function
-- FT: Assembly (CLR) table-valued function
-- IF: In-lined table-function
-- IT: Internal table
-- P: Stored procedure
-- PC: Assembly (CLR) stored-procedure
-- PK: PRIMARY KEY constraint (type is K)
-- RF: Replication filter stored procedure
-- S: System table
-- SN: Synonym
-- SQ: Service queue
-- TA: Assembly (CLR) DML trigger
-- TF: Table function
-- TR: SQL DML Trigger
-- TT: Table type
-- U: User table
-- UQ: UNIQUE constraint (type is K)
-- V: View
-- X: Extended stored procedure