Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 581
  • Last Modified:

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
0
David Little
Asked:
David Little
1 Solution
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now