INFORMATION SCHEMA

Overview

• Introduced in SQL 2000
• Being used to obtain the metadata rather than going back to sys tables
• Conforms to SQL-92 standard

SQL Server Term	SQL-92 Term
Database	Catalog
Owner	Schema
Object	Object
User-defined Data Type	Domain

• Available in all databases

• View Definition can be viewed in master db
• Following are 20 views exposed by SQL Server under INFORMATION_SCHEMA

View Name	Description
Databases
Schemata	Contains information on each database on the SQL Server
Tables and Views
Columns	Contains information on each column in the current database
Tables	Contains information on each relation (table or view) in the current  database
Views	Contains information on each view in the current database
View_Column_Usage	Contains information on each column used by a view in the current database
View_Table_Usage	Contains information on each table used by a view in the current database
Constraints
Check_Constraints	Contains information on each check constraint in the current database
Constraint_Column_Usage	Contains information on each column used by a constraint in the current database
Constraint_Table_Usage	Contains information on each table with a constraint in the current database
Domain_Constraints	Contains information on each user-defined database with a rule attached
Key_Column_Usage	Contains information on each column used by a foreign or primary key in the current database
Referential_Constraints	Contains information on each foreign key constraint in the database
Table_Constraints	Contains information on each table-level constraint in the database
User-Defined Data Types
Column_Domain_Usage	Contains information on each column in the database that has a user-defined data type
Domains	Contains information on each user-defined data type in the current database
Permissions
Column_Privileges	Contains information on each column in the database where a permission has been granted to or granted by the current user
Table_Privileges	Contains information on each table in the database where a permission has been granted to or granted by the current user
Stored Procedures and User-Defined Functions *
Parameters	Contains information for each parameter of a user-defined function or stored procedure in the current database
Routine_Columns	Contains information on each column returned by a user (or system) defined function which returns table values
Routines	Contains information on each stored procedure or user-defined function in the current database

Permission

• If person has access on 10 out of 20 tables then from Information_schema, he/she will be able to see information related to those 10 tables only

• To access system tables, one should have special privilege which is not required for information_schema

Limitation

• Information_schema does not provide any info about Index

Examples

1. Generating Data Dictionary

SELECT      TABLE_NAME AS TableName,

            COLUMN_NAME ColumnName,

            DATA_TYPE DataType,

            ISNULL(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),'') Length,

            IS_NULLABLE IsNullable,

            ISNULL(COLUMN_DEFAULT,'') [Default]

FROM  INFORMATION_SCHEMA.COLUMNS

ORDER BY 1, ORDINAL_POSITION

2. Fetching info about Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

3. Fetching info about parameters of all SPs/Functions

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

4. Fetching info about constraints

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

5. Fetching info about Foreign key

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Delete Vs Truncate

Delete Vs Truncate

	Delete	Truncate
Command	DELETE FROM <<TableName>>	TRUNCATE TABLE <<TableName>>
Speed	Slower	Faster
WHERE	Can be applied	Cannot be applied
IDENTITY	Cannot reset	Will reset
TRIGGER	Will fire	Won’t fire
Transaction Log	Maintains record level log	Maintains page level log
Referential Integrity	If Child table doesn’t have corresponding record from master table then it will allow	Will never ever allow if any referential integrity exists; no matter child table has record or not
Table Variable	Can be deleted	Cannot be truncated
CDC	Will allow	Won’t allow if CDC is enabled on table
Rollback	Can be rolled back	Can be rolled back (yes, this is true, Truncate can be rolled back)

Database Snapshot

Overview

• Introduced in 2005
• Database snapshot is static, read-only view of source database at the time when snapshot was created minus uncommitted transaction
• Dependent on source database
• Physically available on server where source db is there
• Will be accessible as long as source db is there
• Not same as backup
• Multiple snapshots can be created

Behind the Scene

• Operates at data page level
• Before the page of source db is modified for the first time, original page is copied to snapshot
• Creation of snapshot does not take time as well as space as it will be empty
• Gradually it will keep storing all original data pages which have been modified after taking the snapshot

Creating Database Snapshot

• *.mdf and all *.ndf files would be taken into consideration while creating database snapshot
• Extension is .ss
• Syntax

CREATE DATABASE SourceDatabase_Snapshot ON -- Snapshot Name

(

      NAME = SourceDatabase, -- Logical FileName of Original Database

      FILENAME = 'C:\SourceDatabase.ss' -- Location of File Name with .ss ext

)

AS SNAPSHOT OF SourceDatabase -- Database Name

• Now when you check size of this .ss file, it will be same as .mdf file of Original Database, but actually it will empty. When we check the free disk space in My computer of that particular drive, it won’t change after creating snapshot.

Using Database Snapshot

• After creating, snapshot will not be available in Object Explorer, however it will be available in Database dropdown in SSMS.
• We can say Use SnapshotName OR select * from SnapshotName.dbo.TableName

 Use of Database Snapshot

• History Maintenance
• Reporting purpose
• Before doing any major update, we can create a snapshot. If operation was not successful, we can revert back to original stage 

Reverting snapshot to Original Stage

• We can go back to original stage from the point of time when we took the backup.
• Limitations

1. Source db should not contain any read-only OR offline files
2. Only one snapshot should be there
3. After reverting, Transaction log backups won’t work, so full backup has to be taken

• Syntax

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

Drop snapshot

DROP DATABASE SalesSnapshot0600