Re: How to list tables with Primary keys

On Jun 8, 7:59 pm, Danny <dlapi...@xxxxxxxxx> 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?

.

Relevant Pages

  • Re: Data access perfomance
    ... Hi, thanks I kind reduce the time inserting into the database, the problem ... was that I had a primary key on the table, ... >> I can't discuss Oracle, but for SQL Server, the following measures ...
    (microsoft.public.data.oledb)
  • Re: Changing linked SQL Server tables in code
    ... I was working with a test SQL Server ... database that I thought was a clone of production. ... Dim tdf As DAO.TableDef ... can't be updated unless it has a primary key defined. ...
    (comp.databases.ms-access)
  • Re: Identity crisis: GUID VS. Range
    ... I'm using SQL Server automatic identity ranges. ... the primary key then the index can grow a lot. ... central Sql Server 2005 database. ... Assign ranges of primary keys in each subscriber in order to assign ...
    (microsoft.public.sqlserver.replication)
  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... If I keep the primary key, ... >select packet_time,packet_data from packet where ... >have the clustered index (either on PACKET_TIME or on ...
    (microsoft.public.sqlserver.programming)
  • A pk is *both* a physical and a logical object.
    ... Primary key is a logical constraint. ... but SQL Server does not allow you to create a unique or primary ... constraints) by indexing is purely *platform dependent*. ... PKs are part of the ANSI SQL standard *for db implementations*. ...
    (comp.databases.theory)
Loading