Solved

Building Primary Keys from Unique Indexes

Posted on 2003-11-13
7
567 Views
Last Modified: 2009-07-29
We've got an application (MAXIMO) that doesn'd define any primary keys for its tables. It does use unique indexes though for joining tables, just no DRI. We're hoping to implement transactional replication so I have to define primary keys for all the tables we want to use in the subscription.

I know I can run sp_helpindex for each table and get the keys and index type and I can go to each table in EM and "manage indexes" but I was hoping someone had put together something to run and return the tablename, indexcname, index type (unique, etc.) and key columns for the entire database.

David
0
Comment
Question by:David Little
7 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9739640
Try this,

select *
from sysobjects so,sysindexes si
where so.type= 'U' and
      so.id = si.id
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9739671
Or try
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 9739686
Already available using VIEWS


select * from INFORMATION_SCHEMA.Tables
select * from INFORMATION_SCHEMA.Columns
select * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

For a complete list of INFORMATION_SCHEMA's check out:
http://www.ocelot.ca/is.htm
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9739750
None of the standard system tables or views will give you specifically that info.

You can either use sp_helpindex on each index (this could be automated), or sp_help on the table and pull out the index info (this would have to be done manually).
0
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 100 total points
ID: 9740044
This may help

drop proc sp_MyHelpIndex
go

CREATE  Procedure sp_MyHelpIndex
AS
BEGIN

DECLARE @vTableName varchar(50),
        @vSQL varchar(8000)

create table #temp
(

 index_name varchar(1000) null,
 index_desc varchar(1000) null,
 index_keys varchar(1000)null
)

select @vSql = ''

DECLARE table_cursor CURSOR FOR
select  so.name
from    sysobjects so
where   so.type = 'U'

SELECT @vSQL = ' '

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @vTableName

WHILE @@FETCH_STATUS = 0
BEGIN
   
   Select @vSQL = 'INSERT INTO #Temp exec sp_helpindex ' + @vTableName
     
   
   EXEC (@vSQL)
 
   FETCH NEXT FROM table_cursor
   INTO @vTableName

END

CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR

select * from #temp

END

0
 
LVL 2

Author Comment

by:David Little
ID: 9742561
Thanks namasi. The sproc is exactly what I was looking for ... now if I could just get something to automate the create of the PKs! <g>

David
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9744356
Here you go.. I would need another 10,000 point for this :)

CREATE  Procedure sp_MyHelpIndex2
AS
BEGIN

DECLARE @vTableName varchar(50),
        @vSQL varchar(8000),
        @vSQL2 varchar(8000),  
        @index_keys varchar(200)

create table #temp
(

 index_name varchar(1000) null,
 index_desc varchar(1000) null,
 index_keys varchar(1000)null
)

select @vSql = '', @vSql2 = ''

DECLARE table_cursor CURSOR FOR
select  so.name
from    sysobjects so
where   so.type = 'U'

SELECT @vSQL = ' ', @vSql2 = ' '

OPEN table_cursor

FETCH NEXT FROM table_cursor
INTO @vTableName

WHILE @@FETCH_STATUS = 0
BEGIN
   
   delete from #temp

   Select @vSQL = 'INSERT INTO #Temp exec sp_helpindex ' + @vTableName    
   EXEC (@vSQL)

   select @index_keys = index_keys
     from #temp      
    where charindex('clustered, unique', index_desc, 1) > 0

   IF @@ROWCOUNT > 0
   BEGIN
     select @vSql2 = 'ALTER TABLE  ' + @vTableName + ' ADD  PRIMARY KEY  CLUSTERED (' + @index_keys+ ') '
     select @vSQL2
     EXEC(@vSql2)
   END

 
   FETCH NEXT FROM table_cursor
   INTO @vTableName

END

CLOSE TABLE_CURSOR
DEALLOCATE TABLE_CURSOR



END

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now