29 June 2006 09:55 tonyrogerson

Listing User - Functions, SP's, Tables and Views (SQL Standard/SQL 2000/SQL 2005)

The 'SQL standard' way of doing it...

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'FUNCTION'

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'

The SQL Server 2000 way of doing it...

SELECT * -- Stored Procs
FROM sysobjects
WHERE objectproperty( id, N'IsMSShipped' ) = 0
  AND objectproperty( id, N'IsProcedure' ) = 1

SELECT * -- Functions
FROM sysobjects
WHERE objectproperty( id, N'IsMSShipped' ) = 0
  AND ( objectproperty( id, N'IsTableFunction' ) = 1
     OR objectproperty( id, N'IsScalarFunction' ) = 1 )

SELECT * -- User tables
FROM sysobjects
WHERE objectproperty( id, N'IsMSShipped' ) = 0
  AND objectproperty( id, N'IsTable' ) = 1

SELECT * -- Views
FROM sysobjects
WHERE objectproperty( id, N'IsMSShipped' ) = 0
  AND objectproperty( id, N'IsView' ) = 1

The SQL Server 2005 way of doing it is the same as 2000 but we use schemas more, sys.objects instead of sysobjects, oh and object_id and not id...

SELECT * -- Stored Procs
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
  AND objectproperty( object_id, N'IsProcedure' ) = 1

SELECT * -- Functions
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
  AND ( objectproperty( object_id, N'IsTableFunction' ) = 1
     OR objectproperty( object_id, N'IsScalarFunction' ) = 1 )

SELECT * -- User tables
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
  AND objectproperty( object_id, N'IsTable' ) = 1

SELECT * -- Views
FROM sys.objects
WHERE objectproperty( object_id, N'IsMSShipped' ) = 0
  AND objectproperty( object_id, N'IsView' ) = 1

So, which one do I prefer; well its certainly not the INFORMATION_SCHEMA views because they don't scale; if you need to use this type of thing inside stored procedures and performance is a factor then use sysobjects or sys.objects!

 Filed under:

Comments

# Interesting Finds: June 29, 2006 AM edition

29 June 2006 16:14 by Jason Haley

# re: Listing User - Functions, SP's, Tables and Views (SQL Standard/SQL 2000/SQL 2005)

30 June 2006 13:05 by LearnSqlServer.com

When you say that the INFORMATION_SCHEMA views "don't scale", are you referring to their being slow when querying against tons of objects or are you talking about using these in an application that needs to query the system objects? I've worked with both the system tables (sys tables) and INFORMATION_SCHEMA views and this is the first I've heard of someone mentioning that the INFORMATION_SCHEMA views are poor performers. The databases that I've worked on don't have more than a max of 2,000 objects so perhaps I'm not seeing the difference when working with a product like SAP which could have tens of thousands of objects.

I guess that, since the INFORMATION_SCHEMA views get the data I needed and performance was never really an issue for me, I've never done performance tests against whether they were faster or the INFORMATION_SCHEMA views were faster!

Okay....

I just went into SSMS and ran some quick SET STATISTICS TIME ON tests between SQL 2000 and SQL 2005 using the INFORMATION_SCHEMA.VIEWS view (not the TABLES view as you have listed above) and querying the system tables. On my laptop, the INFORMATION_SCHEMA view queries were *marginally* faster.  If you want to be picky, they were practically the same for my small test database (10GB, 500 objects) on a laptop.

Querying the system tables in SQL 2000 took 61 ms total whereas the INFORMATION_SCHEMA.VIEWS query took 47 ms.  

Querying the system tables in SQL 2005 took 54 ms total whereas the INFORMATION_SCHEMA.VIEWS query took 51 ms.

What is about the INFORMATION_SCHEMA views that don't scale? I've just never heard anyone mention that before and I'd like to see what scenarios that I should/should not use them.

Thanks!

Scott Whigham

# re: Listing User - Functions, SP's, Tables and Views (SQL Standard/SQL 2000/SQL 2005)

30 June 2006 22:24 by tonyrogerson

Show me the queries on INFORMATION_SCHEMA you have used and I'll show you how they don't scale.

I developed a key DB component in a enterprise knowledge management system that had dynamic schema that relied on system tables to define what objects I had available, I was very quick to ditch INFORMATION_SCHEMA.

You state stuff in milliseconds which leads me to believe you are looking at CPU stuff because STATS IO gives stuff in READS which is the important measure, anyway - please post your queries and I'll repro the scalability problem; may be I need to write a blog entry around this - might be an idea.

# re: Listing User - Functions, SP's, Tables and Views (SQL Standard/SQL 2000/SQL 2005)

30 June 2006 23:27 by LearnSqlServer.com

My queries were your queries - I just copied and pasted the queries against sysobjects/sys.objects for views and I wrote a SELECT * FROM INFORMATION_SCHEMA.VIEWS (instead of your example using INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'VIEW')

You are correct about my looking at "CPU stuff" and I even mentioned that I turned STATISTICS TIME ON before running all queries. I ran each query multiple times also to avoid the parse/compile/bind time affecting output time. Since you mention the reads, I ran the same queries  through SET STATISTICS IO in both 2000 and 2005 for both using sysobjects/sys.objects and INFORMATION_SCHEMA.VIEWS. Interesting:



SET STATISTICS IO output for sys.objects query for Views in SQL Server 2005:
Table 'syspalnames'. Scan count 0, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 0, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SET STATISTICS IO output for INFORMATION_SCHEMA.VIEWS query in SQL Server 2005:
Table 'sysschobjs'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


SET STATISTICS IO output for sysobjects query for Views in SQL Server 2000:
Table 'sysobjects'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0.

SET STATISTICS IO output for INFORMATION_SCHEMA.VIEWS query in SQL Server 2000:
Table 'syscomments'. Scan count 45, logical reads 141, physical reads 0, read-ahead reads 0.
Table 'sysobjects'. Scan count 1, logical reads 27, physical reads 0, read-ahead reads 0.

It seems as though, for SQL 2000, the INFORMATION_SCHEMA views were worse/less scaling but in 2005, they are the better option. What do you get?