Building Primary Keys from Unique Indexes

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
LVL 2
David LittleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

namasi_navaretnamCommented:
Try this,

select *
from sysobjects so,sysindexes si
where so.type= 'U' and
      so.id = si.id
0
namasi_navaretnamCommented:
Or try
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
0
CJ_SCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
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
namasi_navaretnamCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David LittleAuthor Commented:
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
namasi_navaretnamCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.