Lorenzo Alberton

London, UK   ·   Contact me   ·  

« Articles

Extracting META information from SQL Server (INFORMATION_SCHEMA)

Abstract: How to retrieve META information 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).


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;

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.



12 responses to "Extracting META information from SQL Server (INFORMATION_SCHEMA)"

AUSTIN HIGGS, 12 October 2009 13:42

Thank you for the trigger view with definition. It really helped me !!!!

Raj, 30 March 2010 00:33

Hi Alberton Is there any way to list all the "System Funtions" from DB. like min,max,round,Floor.... etc. possible please send me the queries and syntax Thanks a lot Raj

Kalyson, 13 September 2010 19:46

Hi, Alberton,
Great page! Just wondering if you knew a way to list indexes, too, especially if they are unique indexes...
Thanks! kalyson

Kalyson, 13 September 2010 19:48

Hi, Alberton,
Oh, I see indexes above... sorry!
--k

Larry Mehl, 08 October 2010 16:52

Lorenzo --
Thanks for these valuable utilities.
I need to find views containing a specific table (or, family of tables ... LIKE \\\'bv_%\\\').
Can you give me some direction on how to do that from the system tables?
Thanks in advance,
Larry Mehl

Raja, 15 March 2011 03:03

Hi, Alberton, It is very Help full.. thank u,

Helen Kresl, 26 May 2011 13:41

Lorenzo, I appreciate your post very much. I am wondering if you could show me how to retrieve a list of views containing references to specific column. My task is to find all views that contain a conditional reference to a column called "XYZ" (eg. Where clause comparing XYZ to bit value). This will enable me to give our developers a list of views that will be affected by a change to the "XYZ" column in the source table. Then I have to do that for all stored procedures....but could you assist me, or point me in the right direction, with how to do this with views?

Lorenzo Alberton, 29 May 2011 17:46

@Larry: With this query you can find the table(s) referenced by a view:

 SELECT TABLE_CATALOG,
       TABLE_SCHEMA,
       TABLE_NAME
  FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
 WHERE VIEW_NAME = 'my_view_name'
   AND VIEW_SCHEMA = 'dbo'
   AND VIEW_CATALOG = 'my_db';

By reverting the query, you can select the views containing a table:
 
SELECT VIEW_CATALOG,
       VIEW_SCHEMA,
       VIEW_NAME
  FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
 WHERE TABLE_NAME LIKE 'bv_%';

Lorenzo Alberton, 29 May 2011 17:53

@Helen: With this query you can list all the Views containing references to specific db/table/column:

 
SELECT VIEW_CATALOG,
       VIEW_SCHEMA,
       VIEW_NAME
  FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE 
 WHERE TABLE_CATALOG = 'my_db'
   AND TABLE_SCHEMA = 'dbo'
   AND TABLE_NAME = 'my_table_name'
   AND COLUMN_NAME = 'my_column_name';

dalex1973, 08 December 2011 08:56

There are some limitations for triggers extraction script: 1. Every Syscomments table row contains only 4000 characters of text, so if your trigger`s body length is larger than 4000, you will get several rows in syscomments - and script will return several rows. 2. Script returns only first type of action for trigger. (i.e FOR INSERT, UPDATE you will see INSERT only.)
My version of script:
SELECT sys1.NAME trigger_name, sys2.NAME table_name, (SELECT SS.[text] FROM sys.syscomments SS WHERE ss.id=sys1.id FOR XML PATH(\'\'),TYPE).query(\'text\').value(\'.\',\'nvarchar(max)\') trigger_body, (SELECT TOP 1 ss.encrypted FROM sys.syscomments ss WHERE ss.id=sys1.id) is_encrypted, CASE WHEN OBJECTPROPERTY(sys1.id, \'ExecIsTriggerDisabled\') = 1 THEN 0 ELSE 1 END trigger_enabled, STUFF(CASE WHEN OBJECTPROPERTY(sys1.id, \'ExecIsInsertTrigger\') = 1 THEN \',INSERT\' ELSE \'\' END + CASE WHEN OBJECTPROPERTY(sys1.id, \'ExecIsUpdateTrigger\') = 1 THEN \',UPDATE\' ELSE \'\' END + CASE WHEN OBJECTPROPERTY(sys1.id, \'ExecIsDeleteTrigger\') = 1 THEN \',DELETE\' ELSE \'\' END, 1, 1, \'\') 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 WHERE sys1.xtype = \'TR\';

Amir Hussein Samiani, 26 February 2012 10:11

SET NOCOUNT ON
DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) )
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = \'FOREIGN KEY\'
UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT: SELECT \' ALTER TABLE [\' + ForeignKeyConstraintTableSchema + \'].[\' + ForeignKeyConstraintTableName + \'] DROP CONSTRAINT \' + ForeignKeyConstraintName + \'
GO\' FROM @table
--ADD CONSTRAINT: SELECT \' ALTER TABLE [\' + ForeignKeyConstraintTableSchema + \'].[\' + ForeignKeyConstraintTableName + \'] ADD CONSTRAINT \' + ForeignKeyConstraintName + \' FOREIGN KEY(\' + ForeignKeyConstraintColumnName + \') REFERENCES [\' + PrimaryKeyConstraintTableSchema + \'].[\' + PrimaryKeyConstraintTableName + \'](\' + PrimaryKeyConstraintColumnName + \')
GO\' FROM @table
GO

Amir Hussein Samiani, 26 February 2012 10:18

--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON
DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) )
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = \\\\\\\'FOREIGN KEY\\\\\\\'
UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT: SELECT \\\\\\\' ALTER TABLE [\\\\\\\' + ForeignKeyConstraintTableSchema + \\\\\\\'].[\\\\\\\' + ForeignKeyConstraintTableName + \\\\\\\'] DROP CONSTRAINT \\\\\\\' + ForeignKeyConstraintName + \\\\\\\'
GO\\\\\\\' FROM @table
--ADD CONSTRAINT: SELECT \\\\\\\' ALTER TABLE [\\\\\\\' + ForeignKeyConstraintTableSchema + \\\\\\\'].[\\\\\\\' + ForeignKeyConstraintTableName + \\\\\\\'] ADD CONSTRAINT \\\\\\\' + ForeignKeyConstraintName + \\\\\\\' FOREIGN KEY(\\\\\\\' + ForeignKeyConstraintColumnName + \\\\\\\') REFERENCES [\\\\\\\' + PrimaryKeyConstraintTableSchema + \\\\\\\'].[\\\\\\\' + PrimaryKeyConstraintTableName + \\\\\\\'](\\\\\\\' + PrimaryKeyConstraintColumnName + \\\\\\\')
GO\\\\\\\' FROM @table
GO


Related articles

Latest articles

Filter articles by topic

AJAX, Apache, Book Review, Charset, Cheat Sheet, Data structures, Database, Firebird SQL, Hadoop, Imagick, INFORMATION_SCHEMA, JavaScript, Kafka, Linux, Message Queues, mod_rewrite, Monitoring, MySQL, NoSQL, Oracle, PDO, PEAR, Performance, PHP, PostgreSQL, Profiling, Scalability, Security, SPL, SQL Server, SQLite, Testing, Tutorial, TYPO3, Windows, Zend Framework


Back