Solved

Building Primary Keys from Unique Indexes

Posted on 2003-11-13
7
569 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 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

18 Experts available now in Live!

Get 1:1 Help Now