|
|
Posted by ctotos on 06/12/07 14:51
On Jun 8, 7:59 pm, Danny <dlapi...@gmail.com> wrote:
> Hello,
>
> We imported a bunch of tables from a database and realized that the
> primary keys weren't copied to the destination db. In order to re-
> create the keys, we need to know which tables have them. Is there a
> command that I can use (on the source db) to find out which tables
> contain primary keys? The db has hundreds of tables and I'd rather not
> go through each one to see which has a primary key.
>
> Also, for future reference, is there a way to include the primary key
> on an import?
>
> Thanks,
> Peps
What are all the keys used in a database
http://www.sqlhacks.com/faqs/list_all_keys
USE AdventureWorksLT;
go
SELECT schm.name AS 'Schema', tbl.name AS 'Table'
, KEYS.name AS 'Constraint', KEYS.type_desc AS 'Type'
, cols.name AS 'Column'
FROM sys.key_constraints AS KEYS
JOIN sys.TABLES AS tbl
ON tbl.object_id = KEYS.parent_object_id
JOIN sys.schemas AS schm
ON schm.schema_id = tbl.schema_id
JOIN sys.index_columns AS idxcols
ON idxcols.object_id = tbl.object_id
AND idxcols.index_id = KEYS.unique_index_id
JOIN sys.COLUMNS AS cols
ON cols.object_id = tbl.object_id
AND cols.column_id = idxcols.column_id
ORDER BY 1,2,3,4;
go
AND
What are all the tables without a primary key?
http://www.sqlhacks.com/faqs/no_primary_key
USE sql911;
go
SELECT SCHEMA_NAME(schema_id) AS "Schema", name AS "Table"
FROM sys.TABLES
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
ORDER BY 1,2;
go
This includes samples and explanations on how to do it.
Also new this week:
SQL Server index performance
SQL Server - optimization:index performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server?
What's the structure of a table with MS SQL Server?
Navigation:
[Reply to this message]
|