SQL Server INFORMATION_SCHEMA

Following my tutorials on how to extract meta informations from Firebird SQL and from PostgreSQL, I'm now going to show how to retrieve the same informations from Microsoft SQL Server, using the INFORMATION_SCHEMA, with system tables (sysobjects, sysindexkeys, sysindexes, syscolumns, systypes, etc) and/or with system stored procedures (T-SQL). SQL Server 2005 introduced some catalog views called "sys.*": for completeness, I may use them too in the queries. To list all the views available in the INFORMATION_SCHEMA, you can run the following query from the 'master' database: 

  SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.Views
   WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
ORDER BY TABLE_NAME;

Related: Interbase/Firebird INFORMATION_SCHEMA
Related: Oracle INFORMATION_SCHEMA
Related: PostgreSQL INFORMATION_SCHEMA

Test data

We need a few sample tables, indices and views to test the following queries, so let's create them. We also create a sample TRIGGER and a function.

-- sample data to test SQL Server INFORMATION_SCHEMA

-- TABLE TEST
CREATE TABLE TEST (
  TEST_NAME CHAR(30) NOT NULL,
  TEST_ID INTEGER DEFAULT '0' NOT NULL,
  TEST_DATE TIMESTAMP NOT NULL
);
ALTER TABLE TEST ADD CONSTRAINT PK_TEST PRIMARY KEY (TEST_ID);

-- TABLE TEST2 with some CONSTRAINTs and an INDEX
CREATE TABLE TEST2 (
  ID INTEGER NOT NULL,
  FIELD1 INTEGER,
  FIELD2 CHAR(15),
  FIELD3 VARCHAR(50),
  FIELD4 INTEGER,
  FIELD5 INTEGER,
  ID2 INTEGER NOT NULL
);
ALTER TABLE TEST2 ADD CONSTRAINT PK_TEST2 PRIMARY KEY (ID2);
ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD1ID_IDX UNIQUE (ID, FIELD1);
ALTER TABLE TEST2 ADD CONSTRAINT TEST2_FIELD4_IDX UNIQUE (FIELD4);
CREATE INDEX TEST2_FIELD5_IDX ON TEST2(FIELD5);

-- TABLE NUMBERS
CREATE TABLE NUMBERS (
  NUMBER INTEGER DEFAULT '0' NOT NULL,
  EN CHAR(100) NOT NULL,
  FR CHAR(100) NOT NULL
);

-- TABLE NEWTABLE
CREATE TABLE NEWTABLE (
  ID INT DEFAULT 0 NOT NULL,
  SOMENAME VARCHAR (12),
  SOMEDATE TIMESTAMP NOT NULL,
  SOMEDESCRIPTION VARCHAR(12) NULL
);
ALTER TABLE NEWTABLE ADD CONSTRAINT PKINDEX_IDX PRIMARY KEY (ID);

-- VIEW on TEST
CREATE VIEW "testview"(
  TEST_NAME,
  TEST_ID,
  TEST_DATE
) AS
SELECT *
FROM TEST
WHERE TEST_NAME LIKE 't%';

-- VIEW on NUMBERS
CREATE VIEW "numbersview"(
  NUMBER,
  TRANS_EN,
  TRANS_FR
) AS
SELECT *
FROM NUMBERS
WHERE NUMBER > 100;

-- TRIGGER on NEWTABLE
CREATE TRIGGER TEST_TRIGGER ON NEWTABLE
FOR UPDATE AS
DECLARE @oldName VARCHAR(100)
DECLARE @newId INTEGER
SELECT @oldName = (SELECT somename FROM Deleted)
SELECT @newId = (SELECT id FROM Inserted)
BEGIN
  UPDATE NEWTABLE SET somedescription = @oldName WHERE id = @newId;
END;

-- FUNCTION
CREATE FUNCTION sum_decimal(@Number1 Decimal(6,2), @Number2 Decimal(6,2))
RETURNS Decimal(6,2)
BEGIN
    DECLARE @Result Decimal(6,2)
    SET @Result = @Number1 + @Number2
    RETURN @Result
END;

-- STORED PROCEDURE
CREATE PROC getname
AS
 SELECT SOMENAME FROM newtable;

-- TABLEs for testing CONSTRAINTs
CREATE TABLE testconstraints (
  someid integer NOT NULL,
  somename character varying(10) NOT NULL,
  CONSTRAINT testconstraints_id_pk PRIMARY KEY (someid)
);
CREATE TABLE testconstraints2 (
  ext_id INT NOT NULL,
  modified DATETIME,
  uniquefield VARCHAR(10) NOT NULL,
  usraction INT NOT NULL,
  CONSTRAINT testconstraints_id_fk FOREIGN KEY (ext_id)
      REFERENCES testconstraints (someid)
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT unique_2_fields_idx UNIQUE (modified, usraction),
  CONSTRAINT uniquefld_idx UNIQUE (uniquefield)
)

List TABLEs

Here's the query that will return the names of the tables defined in the current database: 

-- using the sp_tables system stored procedure:

EXEC sp_tables @table_type = "'TABLE'";

-- using INFORMATION_SCHEMA:

  SELECT Owner = TABLE_SCHEMA,
         TableName = TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'
     AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0
ORDER BY TABLE_SCHEMA,
         TABLE_NAME;

-- using the system tables:

SELECT name
  FROM sysobjects
 WHERE xtype = 'U';

-- using the catalog views:

SELECT name FROM sys.tables;

List VIEWs

Here's the query that will return the names of the VIEWs defined in the current database: 

-- using the sp_tables system stored procedure:

EXEC sp_tables @table_type = "'VIEW'", @table_owner = "dbo";

-- using INFORMATION_SCHEMA:

  SELECT Owner = TABLE_SCHEMA,
         TableName = TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'VIEW'
     AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMsShipped') = 0
ORDER BY TABLE_SCHEMA,
         TABLE_NAME;

-- using the system tables:

SELECT name
  FROM sysobjects
 WHERE xtype = 'V';

-- using the catalog views:

SELECT name FROM sys.views;

List users

SELECT DISTINCT loginame
  FROM master..sysprocesses;

-- or

EXEC sp_who 'active';

-- or

SELECT name FROM sysusers;

List INDICES

Here's the query that will return the names of the INDICES defined in the TEST2 table (AFAIK, indices are not covered in the INFORMATION_SCHEMA): 

-- using the sysindexes system view:

SELECT name
  FROM sysindexes
 WHERE OBJECT_NAME(id) = 'TEST2';

-- using the system stored procedures:

EXEC sp_statistics @table_name="TEST2", @is_unique=N;

-- or

EXEC sp_helpindex "TEST2";

List CONSTRAINTs

Here's the query that will return the names of the CONSTRAINTs defined in the TEST2 table: 

-- with INFORMATION_SCHEMA:

SELECT constraint_name,
       constraint_type
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE constraint_catalog = DB_NAME()
   AND table_name = 'TEST2';

-- using the system stored procedures:

EXEC sp_pkeys @table_name="TEST2";   -- PKs
EXEC sp_fkeys @pktable_name="TEST2"; -- FKs
EXEC sp_statistics @table_name='TEST2', @is_unique=Y; -- Unique constraints

-- or

EXEC sp_helpconstraint "TEST2";

Detailed CONSTRAINT info

If you want to retrieve detailed info from any constraint (fields, type, rules, referenced table and fields for FOREIGN KEYs, etc.) given its name and table, here's the query to do so: 

   SELECT k.table_name,
          k.column_name field_name,
          c.constraint_type,
          CASE c.is_deferrable WHEN 'NO' THEN 0 ELSE 1 END 'is_deferrable',
          CASE c.initially_deferred WHEN 'NO' THEN 0 ELSE 1 END 'is_deferred',
          rc.match_option 'match_type',
 		  rc.update_rule 'on_update',
          rc.delete_rule 'on_delete',
          ccu.table_name 'references_table',
          ccu.column_name 'references_field',
          k.ordinal_position 'field_position'
     FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
     LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
       ON k.table_name = c.table_name
      AND k.table_schema = c.table_schema
      AND k.table_catalog = c.table_catalog
      AND k.constraint_catalog = c.constraint_catalog
      AND k.constraint_name = c.constraint_name
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
       ON rc.constraint_schema = c.constraint_schema
      AND rc.constraint_catalog = c.constraint_catalog
      AND rc.constraint_name = c.constraint_name
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
       ON rc.unique_constraint_schema = ccu.constraint_schema
      AND rc.unique_constraint_catalog = ccu.constraint_catalog
      AND rc.unique_constraint_name = ccu.constraint_name
    WHERE k.constraint_catalog = DB_NAME()
      AND k.table_name = 'testconstraints2'
      AND k.constraint_name = 'testconstraints_id_fk'
 ORDER BY k.constraint_name,
          k.ordinal_position

List table fields

Here's the query that will return the names of the fields of the TEST2 table: 

   SELECT c.name
     FROM syscolumns c
LEFT JOIN sysobjects o ON c.id = o.id
    WHERE o.name = 'test2'

-- or

SELECT c.name
  FROM sys.columns c
  JOIN sys.objects o ON c.object_id = o.object_id
 WHERE o.name = 'test2'

-- with INFORMATION_SCHEMA:

SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_name = 'test2';

Detailed table field info

If you want some more info about the field definitions, you can retrieve a larger subset of the fields available in the schema: 

   SELECT OBJECT_NAME(c.id) as table_name,
          c.name,
          t.name AS data_type,
          c.isnullable,
          com.text AS default_text,
          c.length,
          c.prec AS numeric_precision,
          c.scale AS numeric_scale,
          c.colorder
     FROM syscolumns c
LEFT JOIN systypes t ON c.type = t.type AND c.xtype = t.xtype
LEFT JOIN syscomments com ON com.id = c.cdefault
    WHERE OBJECT_NAME(c.id) = 'newtable'
 ORDER BY c.colorder;

-- or

SELECT o.name AS table_name,
       c.name,
       type_name(c.system_type_id) AS DATA_TYPE,
       c.is_nullable,
       object_definition(c.default_object_id) AS column_default,
       ColumnProperty(c.object_id, c.name, 'charmaxlen') AS length,
       c.precision AS numeric_precision,
       odbcscale(c.system_type_id, c.scale) AS numeric_scale,
       CollationProperty(c.collation_name, 'sqlcharsetname') AS character_set_name,
       c.collation_name,
       ColumnProperty(c.object_id, c.name, 'ordinal') AS ordinal_position
  FROM sys.objects o
  JOIN sys.columns c ON c.object_id = o.object_id
 WHERE o.name = 'newtable'

-- with INFORMATION_SCHEMA:

  SELECT t.table_name,
         c.column_name 'name',
         c.data_type 'type',
         c.is_nullable,
         c.column_default,
         c.character_maximum_length 'length',
         c.numeric_precision,
         c.numeric_scale,
         c.character_set_name,
         c.collation_name,
         c.ordinal_position
    FROM INFORMATION_SCHEMA.TABLES t,
         INFORMATION_SCHEMA.COLUMNS c
   WHERE t.table_name = c.table_name
     AND t.table_name = 'newtable'
ORDER BY c.ordinal_position

List TRIGGERs

SELECT o.name
  FROM sysobjects o
 WHERE xtype = 'TR'
   AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0;

-- or

SELECT name
  FROM sys.triggers
 WHERE is_ms_shipped = 0;

List only the triggers for a given table: 

SELECT o.name
  FROM sysobjects o
 WHERE xtype = 'TR'
   AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
   AND object_name(parent_obj) = 'newtable';

-- or

SELECT name
  FROM sys.triggers
 WHERE is_ms_shipped = 0
   AND object_name(parent_id) = 'newtable';

Detailed TRIGGER info

Show more informations about the trigger definitions: 

SELECT sys1.name trigger_name,
       sys2.name table_name,
       c.text trigger_body,
       c.encrypted is_encripted,
       CASE
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsTriggerDisabled') = 1
         THEN 0 ELSE 1
       END trigger_enabled,
       CASE
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsertTrigger') = 1 THEN 'INSERT'
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsUpdateTrigger') = 1 THEN 'UPDATE'
         WHEN OBJECTPROPERTY(sys1.id, 'ExecIsDeleteTrigger') = 1 THEN 'DELETE'
       END trigger_event,
       CASE WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsteadOfTrigger') = 1
         THEN 'INSTEAD OF' ELSE 'AFTER'
       END trigger_type
  FROM sysobjects sys1
  JOIN sysobjects sys2 ON sys1.parent_obj = sys2.id
  JOIN syscomments c ON sys1.id = c.id
 WHERE sys1.xtype = 'TR';

List FUNCTIONs

SELECT name
  FROM sysobjects
 WHERE xtype IN ('TF', 'FN', 'IF');

-- with INFORMATION_SCHEMA:

SELECT routine_name
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE routine_type = N'FUNCTION';

List STORED PROCEDUREs

SELECT name
  FROM sysobjects
 WHERE xtype IN ('P', 'RF', 'X', 'PC');

-- or

SELECT name
  FROM sys.procedures;

-- with INFORMATION_SCHEMA:

SELECT routine_name
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE routine_type = N'PROCEDURE';

What else?

If you'd like to see some other example queries, or have some comments and/or suggestions, just drop me a mail (you can find my address in the footer of this page) and I'll add them to this list.
HTH.

Socially Bookmark

Delicious     Digg     Furl     Reddit     Cosmos

Spurl     Blinklist     Simpy     Blogmarks     YahooMyWeb


SQL Server 2005 database