|
|
|
|
Primary Key is important constraint to maintain integrrity and speed. Here is the query if you want to find all tables without Primary key.
Note: Please change Northwind with your DB name.
All tables without primary key from Northwind Database |
 Click here to copy the following block | Select * from Northwind.information_schema.tables
where (table_name like '%' )
and table_name not in (
SELECT COL.TABLE_NAME as PKI_TABLENAME
FROM Northwind.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as CONST
JOIN Northwind.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as COL
ON CONST.TABLE_SCHEMA = COL.TABLE_SCHEMA
AND CONST.TABLE_NAME = COL.TABLE_NAME
AND CONST.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
WHERE CONST.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND CONST.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
AND TABLE_TYPE='BASE TABLE' |
| All tables with primary key from Northwind Database |
| Click here to copy the following block | Select * from Northwind.information_schema.tables
where (table_name like '%' )
and table_name in (
SELECT COL.TABLE_NAME as PKI_TABLENAME
FROM Northwind.INFORMATION_SCHEMA.TABLE_CONSTRAINTS as CONST
JOIN Northwind.INFORMATION_SCHEMA.KEY_COLUMN_USAGE as COL
ON CONST.TABLE_SCHEMA = COL.TABLE_SCHEMA
AND CONST.TABLE_NAME = COL.TABLE_NAME
AND CONST.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
WHERE CONST.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND CONST.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
AND TABLE_TYPE='BASE TABLE' |
|
|
|
|
|
|
Submitted By :
Nayan Patel
(Member Since : 5/26/2004 12:23:06 PM)
|
 |
|
|
Job Description :
He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting. |
|
View all (893) submissions by this author
(Birth Date : 7/14/1981 ) |
|
|