Tuesday, February 28, 2012

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

  1. Fetching info about Tables

SELECT * FROM INFORMATION_SCHEMA.TABLES

  1. Fetching info about parameters of all SPs/Functions

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS

  1. Fetching info about constraints

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

  1. Fetching info about Foreign key

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS


Tuesday, February 21, 2012

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)