Catalog View over Information_Schema in Microsoft SQL Server 2008

Basically Catalog View was first introduced in SQL Server 2005. It provided insight view of database objects. Previously way back, we used to get this kind of insight tour via system tables, system SPs or Information_Schema view. Catalog has few advantages over others.

Many of the old System SPs and System tables are removed and Information_Schema is given in newer version also but just for the backward compatibility. It will be deprecated in future version for sure. Microsoft insists to use Catalog view over Information_Schema. You can have much better functionality in Catalog view and can do almost everything which you can do with Information_Schema. Let us look at one simple example which will show you columns of all tables or for those table which meets criteria in WHERE clause in SELECT statement.

–use of catalog view
SELECT
s.name AS schema_name,
t.name AS table_name,
t.type_desc AS table_type,
c.name AS column_name,
c.column_id,
ty.name AS data_type_name,
c.is_nullable
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id
AND c.user_type_id = ty.user_type_id
where s.name=‘Production’ and t.name=‘ProductReview’
GO
–use of Information_Schema
select
t.TABLE_SCHEMA as schema_name,
t.TABLE_NAME as table_name,
t.TABLE_TYPE as table_type,
c.COLUMN_NAME as column_name,
c.ORDINAL_POSITION as column_id,
c.DATA_TYPE as data_type_name,
c.IS_NULLABLE as is_nullable
from INFORMATION_SCHEMA.TABLES as t
join INFORMATION_SCHEMA.COLUMNS as c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA=‘Production’ and t.TABLE_NAME=‘ProductReview’
 BTW, I have written very small note of Catalog View in past too, if you would like to read it, Click Here.

I forced myself to write this article as I still see people are so used to with Information_Schema even this is almost 5 years SQL Server 2005 introduced and even two years SQL Server 2008 is introduced (since beta). I would suggest please stop using Information_Schema and grab the power of new Catalog View.



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

Catalog View over Information_Schema in Microsoft SQL Server 2008

Basically Catalog View was first introduced in SQL Server 2005. It provided insight view of database objects. Previously way back, we used to get this kind of insight tour via system tables, system SPs or Information_Schema view. Catalog has few advantages over others.

Many of the old System SPs and System tables are removed and Information_Schema is given in newer version also but just for the backward compatibility. It will be deprecated in future version for sure. Microsoft insists to use Catalog view over Information_Schema. You can have much better functionality in Catalog view and can do almost everything which you can do with Information_Schema. Let us look at one simple example which will show you columns of all tables or for those table which meets criteria in WHERE clause in SELECT statement.

–use of catalog view
SELECT
s.name AS schema_name,
t.name AS table_name,
t.type_desc AS table_type,
c.name AS column_name,
c.column_id,
ty.name AS data_type_name,
c.is_nullable
FROM sys.schemas s
INNER JOIN sys.tables t
ON s.schema_id = t.schema_id
INNER JOIN sys.columns c
ON t.object_id = c.object_id
INNER JOIN sys.types ty
ON c.system_type_id = ty.system_type_id
AND c.user_type_id = ty.user_type_id
where s.name=‘Production’ and t.name=‘ProductReview’
GO
–use of Information_Schema
select
t.TABLE_SCHEMA as schema_name,
t.TABLE_NAME as table_name,
t.TABLE_TYPE as table_type,
c.COLUMN_NAME as column_name,
c.ORDINAL_POSITION as column_id,
c.DATA_TYPE as data_type_name,
c.IS_NULLABLE as is_nullable
from INFORMATION_SCHEMA.TABLES as t
join INFORMATION_SCHEMA.COLUMNS as c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA=‘Production’ and t.TABLE_NAME=‘ProductReview’
 BTW, I have written very small note of Catalog View in past too, if you would like to read it, Click Here.

I forced myself to write this article as I still see people are so used to with Information_Schema even this is almost 5 years SQL Server 2005 introduced and even two years SQL Server 2008 is introduced (since beta). I would suggest please stop using Information_Schema and grab the power of new Catalog View.



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Very useful Catalog View in SQL Server 2005:

Catalog View is used to get very crucial and important information about your database and server. There are lots of useful Catalog Views are available in Microsoft SQL Server 2005. Herewith, I am going to explain some of them.
Sys.Databases: this catalog view is useful to get information about all the available databases in sql server instance. It will provide you useful information like database name, database id, date of creation, compatibility level (database is compatible to which version of SQL Server, 90 is for SQL Server 2005), collation name (which language we can use in database) etc.
You can use following query for Sys.Databases:
Select * from sys.databases
GO
Sys.Database_files: this catalog view will give us information about particular database’s file. It will give you list of all files which have been used for database. You will get information about which type of files are there for particular database like LOG, DATA and FULLTEXT etc. Along with type of file, it will give you file name, its physical address on the server, state description whether it is ONLINE or OFFLINE, size of each file, growth rate of file etc.
You can use following query for Sys.Database_Files:
–return all files for Adventureworks database
USE AdventureWorks
SELECT * FROM sys.database_files
GO
Sys.Objects: This catalog will cater your need about all the objects available in database. You can get object name, its parent id(if there is any), type description like whether object is system table or user table or foreign key etc.
Below query will return all user tables:
USE AdventureWorks
SELECT * FROM Sys.Objects WHERE Type_Desc=‘User_Table’
GO
Sys.Key_Constraint: this catalog view will give you details about primary key in your database. You can get Parent name, constraint name, and schema id and type description by below query.
SELECT Object_Name(Parent_Object_Id) as ParentName,name,Schema_id,type_desc FROM sys.key_constraints
Sys.Foreign_Key_Columns: You will get information about available foreign key in your database along with its table name and referenced table name with following query.
SELECT Object_Name(Constraint_Object_Id) as ‘Name’,Object_Name(Parent_Object_Id) AS ‘TableName’,Object_Name(Referenced_Object_id) AS ‘Referenced Table’ FROM sys.foreign_key_columns
Sys.Columns: You will get information about all columns available in all tables for selected database. You will get Table name, column name, and collation name, null value status for column, maximum length and precision of columns by following query.
SELECT Object_Name(Object_Id) as ‘TableName’,Name as ‘ColumnName’,collation_Name,is_nullable,max_length,precision FROM Sys.Columns
There many more catalog views are available in SQL Server 2005, I will cover few more in my later article.
Reference: Ritesh Shah