When querying the SQL Server metadata should we use INFORMATION_SCHEMA or system views?
In my previous post I described two methods for searching a SQL Server database for a column name. One method was to query the information schema and the other to query the sys views. This article will describe features of both views of the metadata.
Both the INFORMATION_SCHEMA and sys views can be found within the System Views folder in each database on the server:
Both methods can answer the same questions. If we need information about all of the tables in a database then we could run the following which would provide similar results:
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE TYPE = ‘BASE TABLE’
ORDER BY 3
SELECT * FROM SYSOBJECTS WHERE XTYPE = ‘U’
ORDER BY 1
If we wanted the information about the views in a database then we could execute the following:
SELECT * FROM INFORMATION_SCHEMA.VIEWS
SELECT * FROM SYSOBJECTS WHERE XTYPE = ‘V’
So which method of querying the metadata should we use? The information schema views are ANSI standard and will not change between SQL Server versions. The system views however are specific to SQL Server and their internal structure may change between versions. This is less likely in recent versions though.
The main problem with querying the information schema views is that there are certain objects it ignores or doesn’t query completely. One example of this would be computed columns:
CREATE TABLE DBO.MYTABLE
( net_sales INT,
gross_sales AS (CONVERT(INT, net_sales * 1.2))
If we queried net_sales and gross_sales via INFORMATION_SCHEMA.COLUMNS they would both like look ordinary integer columns. The sys.columns system view however includes a flag called is_computed. This query would include the computed gross_sales column:
WHERE is_computed = 1
Another example is if you are trying to use INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION to return or just search within the definition for a stored procedure or function. This column is truncated and will only return the first 4000 characters.
In summary, even though for most tasks we could use either methods, it is perhaps safer to query the system views rather than the information schema.