Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT t.TABLE_NAME As 'Table Name',
- Keys.COLUMN_NAME AS 'Primary Key'
- FROM INFORMATION_SCHEMA.TABLES t
- left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS Constraints
- on t.TABLE_NAME = Constraints.Table_name
- and t.Table_Schema = Constraints.Table_Schema
- left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS Keys
- ON Constraints.TABLE_NAME = Keys.TABLE_NAME
- and Constraints.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
- and Constraints.CONSTRAINT_TYPE = 'PRIMARY KEY'
- SELECT T.TABLE_NAME As 'Table Name'
- , (
- Select K1.COLUMN_NAME
- From INFORMATION_SCHEMA.TABLE_CONSTRAINTS As C1
- Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE As K1
- On C1.TABLE_SCHEMA = K1.TABLE_SCHEMA
- And C1.TABLE_NAME = K1.TABLE_NAME
- And C1.CONSTRAINT_NAME = K1.CONSTRAINT_NAME
- Where C1.CONSTRAINT_TYPE = 'PRIMARY KEY'
- And T.TABLE_SCHEMA = C1.TABLE_SCHEMA
- And T.TABLE_NAME = C1.TABLE_NAME
- )As PrimaryKeyColumns
- FROM INFORMATION_SCHEMA.TABLES As T
- SELECT t.TABLE_NAME As 'Table Name',
- Keys.COLUMN_NAME AS 'Primary Key'
- FROM INFORMATION_SCHEMA.TABLES t
- left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS Constraints
- on t.TABLE_NAME = Constraints.Table_name
- and t.Table_Schema = Constraints.Table_Schema
- and Constraints.CONSTRAINT_TYPE = 'PRIMARY KEY'
- left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS Keys
- ON Constraints.TABLE_NAME = Keys.TABLE_NAME
- and Constraints.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
- SELECT T.TABLE_NAME As 'Table Name'
- , Stuff(
- (
- Select ', ' + K1.COLUMN_NAME
- From INFORMATION_SCHEMA.TABLE_CONSTRAINTS As C1
- Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE As K1
- On C1.TABLE_SCHEMA = K1.TABLE_SCHEMA
- And C1.TABLE_NAME = K1.TABLE_NAME
- And C1.CONSTRAINT_NAME = K1.CONSTRAINT_NAME
- Where C1.CONSTRAINT_TYPE = 'PRIMARY KEY'
- And T.TABLE_SCHEMA = C1.TABLE_SCHEMA
- And T.TABLE_NAME = C1.TABLE_NAME
- For Xml Path(''), type
- ).value('.', 'nvarchar(max)')
- , 1, 2, '') As PrimaryKeyColumns
- FROM INFORMATION_SCHEMA.TABLES As T
- SELECT T.TABLE_NAME As 'Table Name'
- , Keys.COLUMN_NAME AS 'Primary Key'
- FROM INFORMATION_SCHEMA.TABLES As T
- Left Join( INFORMATION_SCHEMA.TABLE_CONSTRAINTS Constraints
- Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE As Keys
- On Constraints.TABLE_SCHEMA = Keys.TABLE_SCHEMA
- And Constraints.TABLE_NAME = Keys.TABLE_NAME
- And Constraints.CONSTRAINT_NAME = Keys.CONSTRAINT_NAME
- And Constraints.CONSTRAINT_TYPE = 'PRIMARY KEY' )
- On T.TABLE_SCHEMA = Constraints.TABLE_SCHEMA
- And T.TABLE_NAME = Constraints.TABLE_NAME
- static void Main(string[] args)
- {
- Server server = new Server("serverName");
- Database db = server.Databases["DatabaseName"];
- string tableName = "TableName";
- Table table = db.Tables[tableName];
- if (table != null)
- {
- Console.WriteLine("Table: {0}", tableName);
- if (table.Columns.Count > 0)
- {
- Console.WriteLine(" Primary Key Columns:");
- foreach (Column column in table.Columns)
- {
- if (column.InPrimaryKey)
- {
- Console.WriteLine(string.Format(" {0}", column.Name));
- }
- }
- }
- else
- {
- Console.WriteLine(" No primary key.", tableName);
- }
- }
- Console.WriteLine("Press ENTER to exit...");
- Console.ReadLine();
- }
- select
- TABLE_SCHEMA,
- TABLE_NAME
- from
- INFORMATION_SCHEMA.TABLES
- where
- objectproperty(object_id(table_name),'TableHasPrimaryKey')=0
- ORDER BY
- TABLE_NAME
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement