DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to list columns with identity, primary key and foreign key flags?

Execute the following Microsoft SQL Server T-SQL script for listing table column meta data including column names, TRUE/FALSE (1/0) flags for IsIdentity, IsPrimaryKey and IsForeignKey properties.

-- SQL table column meta data - SQL table columns - int identity - Primary Key constraints

-- SQL ISPRIMARYKEY - SQL ISFOREIGNKEY - SQL ISIDENTITY - SQL INFORMATION_SCHEMA views

-- SQL columnproperty - SQL data dictionary - Foreign Key constraints

USE AdventureWorks2008;

SELECT   c.TABLE_CATALOG                            AS DatabaseName,

         c.TABLE_SCHEMA                             AS SchemaName,

         c.TABLE_NAME                               AS TableName,

         c.COLUMN_NAME                              AS ColumnName,

         Columnproperty(Object_id(c.TABLE_SCHEMA + '.' + c.TABLE_NAME),

                        c.COLUMN_NAME,'ISIDENTITY') AS IsIdentity,

         CASE

           WHEN CONSTRAINT_NAME IN (SELECT NAME

                                    FROM   sys.objects

                                    WHERE  TYPE = 'PK') THEN 1

           ELSE 0

         END AS IsPrimaryKey,

         CASE

           WHEN CONSTRAINT_NAME IN (SELECT NAME

                                    FROM   sys.objects

                                    WHERE  TYPE = 'F') THEN 1

           ELSE 0

         END AS IsForeignKey

FROM     INFORMATION_SCHEMA.TABLES t

         INNER JOIN INFORMATION_SCHEMA.COLUMNS c

             ON c.TABLE_CATALOG = t.TABLE_CATALOG

              AND c.TABLE_SCHEMA = t.TABLE_SCHEMA

              AND c.TABLE_NAME = t.TABLE_NAME

         LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE u

           ON c.TABLE_CATALOG = u.TABLE_CATALOG

              AND c.TABLE_SCHEMA = u.TABLE_SCHEMA

              AND c.TABLE_NAME = u.TABLE_NAME

              AND c.COLUMN_NAME = u.COLUMN_NAME

WHERE TABLE_TYPE='BASE TABLE'

ORDER BY SchemaName,

         TableName,

         c.ORDINAL_POSITION

GO

 

-- Partial results 

 

DatabaseName SchemaName TableName ColumnName IsIdentity IsPrimaryKey IsForeignKey
AdventureWorks2008 Sales SalesOrderDetail SalesOrderID 0 1 0
AdventureWorks2008 Sales SalesOrderDetail SalesOrderID 0 0 1
AdventureWorks2008 Sales SalesOrderDetail SalesOrderDetailID 1 1 0
AdventureWorks2008 Sales SalesOrderDetail CarrierTrackingNumber 0 0 0
AdventureWorks2008 Sales SalesOrderDetail OrderQty 0 0 0
AdventureWorks2008 Sales SalesOrderDetail ProductID 0 0 1
AdventureWorks2008 Sales SalesOrderDetail SpecialOfferID 0 0 1
AdventureWorks2008 Sales SalesOrderDetail UnitPrice 0 0 0
AdventureWorks2008 Sales SalesOrderDetail UnitPriceDiscount 0 0 0
AdventureWorks2008 Sales SalesOrderDetail LineTotal 0 0 0
AdventureWorks2008 Sales SalesOrderDetail rowguid 0 0 0
AdventureWorks2008 Sales SalesOrderDetail ModifiedDate 0 0 0
AdventureWorks2008 Sales SalesOrderHeader SalesOrderID 1 1 0
AdventureWorks2008 Sales SalesOrderHeader RevisionNumber 0 0 0
AdventureWorks2008 Sales SalesOrderHeader OrderDate 0 0 0
AdventureWorks2008 Sales SalesOrderHeader DueDate 0 0 0
AdventureWorks2008 Sales SalesOrderHeader ShipDate 0 0 0
AdventureWorks2008 Sales SalesOrderHeader Status 0 0 0
AdventureWorks2008 Sales SalesOrderHeader OnlineOrderFlag 0 0 0
AdventureWorks2008 Sales SalesOrderHeader SalesOrderNumber 0 0 0
AdventureWorks2008 Sales SalesOrderHeader PurchaseOrderNumber 0 0 0
AdventureWorks2008 Sales SalesOrderHeader AccountNumber 0 0 0
AdventureWorks2008 Sales SalesOrderHeader CustomerID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader SalesPersonID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader TerritoryID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader BillToAddressID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader ShipToAddressID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader ShipMethodID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader CreditCardID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader CreditCardApprovalCode 0 0 0
AdventureWorks2008 Sales SalesOrderHeader CurrencyRateID 0 0 1
AdventureWorks2008 Sales SalesOrderHeader SubTotal 0 0 0
AdventureWorks2008 Sales SalesOrderHeader TaxAmt 0 0 0
AdventureWorks2008 Sales SalesOrderHeader Freight 0 0 0
AdventureWorks2008 Sales SalesOrderHeader TotalDue 0 0 0
AdventureWorks2008 Sales SalesOrderHeader Comment 0 0 0
AdventureWorks2008 Sales SalesOrderHeader rowguid 0 0 0
AdventureWorks2008 Sales SalesOrderHeader ModifiedDate 0 0 0

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE