David Little
asked on
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
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
Or try
select * from INFORMATION_SCHEMA.TABLE_C ONSTRAINTS
select * from INFORMATION_SCHEMA.TABLE_C
Already available using VIEWS
select * from INFORMATION_SCHEMA.Tables
select * from INFORMATION_SCHEMA.Columns
select * from INFORMATION_SCHEMA.CHECK_C ONSTRAINTS
select * from INFORMATION_SCHEMA.KEY_COL UMN_USAGE
For a complete list of INFORMATION_SCHEMA's check out:
http://www.ocelot.ca/is.htm
select * from INFORMATION_SCHEMA.Tables
select * from INFORMATION_SCHEMA.Columns
select * from INFORMATION_SCHEMA.CHECK_C
select * from INFORMATION_SCHEMA.KEY_COL
For a complete list of INFORMATION_SCHEMA's check out:
http://www.ocelot.ca/is.htm
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
David
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
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
select *
from sysobjects so,sysindexes si
where so.type= 'U' and
so.id = si.id