I was trying to get all user-defined objects in SQL Server.
And tried to make it compatible with 2000,2005 and 2008.
Seems it wasn't easy as in Oracle, especially for stored procedures and functions. If you know a way to distinguish user-defined procedure and system procedure, please share.
select count(*) as 'UserTables'
from sysobjects
where type in ('U')
select count(*) as 'UserIndexes'
from sysindexes i
INNER JOIN sysobjects AS o ON i.id=o.id
where o.type='U'
select count(*) as 'UserTriggers'
from sysobjects o1
INNER JOIN sysobjects AS o2 ON o1.parent_obj=o2.id
where o1.type in ('TR', 'TA')
AND o2.TYPE='U'
select count(*) as 'UserViews'
from sysobjects
where type in ('V')
and name not like 'sys%'
select count(*) as 'StoredProcedures'
from sysobjects
where type in ('P','PC','X')
select count(*) as 'Functions'
from sysobjects
where type in ('FN','FS','FT','TF','AF','IF')
Reference: sysobjects.type
Common Object Types in SQL 2000, 2005 and 2008
C = CHECK constraint
D = DEFAULT (constraint or stand-alone)
F = FOREIGN KEY constraint
FN = SQL scalar function
IF = SQL inline table-valued function
P = SQL stored procedure
PK = PRIMARY KEY constraint
S = System base table
TF = SQL table-valued-function
TR = SQL DML trigger
U = Table (user-defined)
UQ = UNIQUE constraint
V = View
X = Extended stored procedure
Common Object Types in SQL 2005 and 2008
AF = Aggregate function (CLR)
FS = Assembly (CLR) scalar function
FT = Assembly (CLR) table-valued function
IT = Internal table
PC = Assembly (CLR) stored procedure
R = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
New Object Types in SQL 2008
PG = Plan guide
