Finding primary keys and missing primary keys in SQL Server http://www.mssqltips.com/tip.asp?tip=1237Written By: Edgewood Solutions Engineers -- 5/4/......
Finding primary keys and missing primary keys in SQL Server
http://www.mssqltips.com/tip.asp?tip=1237
Written By: Edgewood Solutions Engineers -- 5/4/2007 -- 0 comments
|
Get new tips every day - Don't miss out - Click to find out more
|
Problem
One design aspect that all tables should have is a primary key. The primary key is the main entry way into your dataset, so that when you access your data you are guaranteed to only affect one row of data. Having primary keys are not only a good design feature they also play an important role in replication and data updates especially when there may be duplicate rows of data. So how can you determine what tables have primary keys and what tables do not have primary keys?
Solution
As mentioned above, primary keys guarantee a unique row of data in your table. Some of the design aspects of a primary key are as follows:
- can be one or more columns
- column values can not be null
- the column or combination of columns must be unique
- there can only be one primary key on a table
In the past there have been other tips that focus on all indexes that exist in the database, but here we will take a different look at tables that have primary keys and tables that do not have primary keys. For SQL 2005 this is pretty easy to do now ,by using the sys.key_constraints catalog views, but with SQL 2000 it is a bit cryptic to get this information.
Query 1 - Tables with primary keys
SQL 2005
SELECT c.name, b.name, a.name
FROM sys.key_constraints a
INNER JOIN sys.tables b ON a.parent_object_id = b.OBJECT_ID
1/4 1 2 3 4 下一页 尾页 |
如果您需转载 查找表主键和缺少主键的表，请注明来自LinuxGoo.com，其版权归原作者所有。请广大网友留言时遵纪守法，使用文明用语。如果您在应用中有什么问题，请在下面留言，我们会尽快解答。
|
