Solved

Building Primary Keys from Unique Indexes

Posted on 2003-11-13
7
570 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 69

Expert Comment

by:Scott Pletcher
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle DB monitor SW 21 48
SQL Syntax 5 34
Live Storage Vmotion VMs with shared VMDK 10 56
what are the unique tables in SQL master database 5 58
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 …
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

776 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