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
Advertisements

12 Responses to “Very useful Catalog View in SQL Server 2005:”

  1. Michael Wacht Says:

    I am trying to find the table that contains the columns that are a part of primary or unique key constraints.I thought that sys.key_constraints would contain it, but I was apparently mistaken.Thanks!

  2. Michael Wacht Says:

    I am trying to find the table that contains the columns that are a part of primary or unique key constraints.I thought that sys.key_constraints would contain it, but I was apparently mistaken.Thanks!

  3. Michael Wacht Says:

    Figured it out.I was looking in the sys tables when I should have been checking the information_schemaINFORMATION_SCHEMA.KEY_COLUMN_USAGE contained the information I was seeking

  4. Michael Wacht Says:

    Figured it out.I was looking in the sys tables when I should have been checking the information_schemaINFORMATION_SCHEMA.KEY_COLUMN_USAGE contained the information I was seeking

  5. Ritesh Shah Says:

    Good to know that you figured it out 🙂

  6. Ritesh Shah Says:

    Good to know that you figured it out 🙂

  7. Ritesh Shah Says:

    this one is also helpful to find PK and FK relationship… query is written by Paul Nielson:select fko.name as [FK Name], fk.constraint_column_id as [Col Order], fks.name + '.' + fkt.name as [FK table], pc.name as [FK column], rcs.name + '.' + rct.name as [PK table], rc.name as [PK column] from sys.foreign_key_columns fk — FK columns join sys.columns pc on fk.parent_object_id = pc.object_id and fk.parent_column_id = pc.column_id join sys.objects fkt on pc.object_id = fkt.object_id join sys.schemas as fks on fks.schema_id = fkt.schema_id — referenced PK columns join sys.columns rc on fk.referenced_object_id = rc.object_id and fk.referenced_column_id = rc.column_id join sys.objects rct on rc.object_id = rct.object_id join sys.schemas as rcs on rcs.schema_id = rct.schema_id — foreign key constraint name join sys.objects fko on fk.constraint_object_id = fko.object_id –and fk.referenced_column_id = rc.column_id order by fko.name, fk.constraint_column_id

  8. Ritesh Shah Says:

    this one is also helpful to find PK and FK relationship… query is written by Paul Nielson:select fko.name as [FK Name], fk.constraint_column_id as [Col Order], fks.name + '.' + fkt.name as [FK table], pc.name as [FK column], rcs.name + '.' + rct.name as [PK table], rc.name as [PK column] from sys.foreign_key_columns fk — FK columns join sys.columns pc on fk.parent_object_id = pc.object_id and fk.parent_column_id = pc.column_id join sys.objects fkt on pc.object_id = fkt.object_id join sys.schemas as fks on fks.schema_id = fkt.schema_id — referenced PK columns join sys.columns rc on fk.referenced_object_id = rc.object_id and fk.referenced_column_id = rc.column_id join sys.objects rct on rc.object_id = rct.object_id join sys.schemas as rcs on rcs.schema_id = rct.schema_id — foreign key constraint name join sys.objects fko on fk.constraint_object_id = fko.object_id –and fk.referenced_column_id = rc.column_id order by fko.name, fk.constraint_column_id

  9. Michael Wacht Says:

    Thanks for the PK/FK query!

  10. Michael Wacht Says:

    Thanks for the PK/FK query!

  11. Ritesh Shah Says:

    you are welcome always

  12. Ritesh Shah Says:

    you are welcome always


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: