I needed to add a
Primary Key to each of my tables within my Coda Financials Database. This was
required so I could institute replication...
Fortunately each of
the tables I required for replication have Unique Clustered Indexes on them. This
obviously meant I could add a Primary Key to each of these tables using the
Index Key Columns from these Unique Clustered Indexes.
I wanted to go down
this route because the application checksums all rows in each table, so if I
added an extra Primary Key column it would mean having to run Coda's FixCheck
application over all the tables. This way I don't need to do this.
I came up with the
below script to save me writing each individual ALTER Table
statement:
DECLARE @IndexInfoTemp TABLE
(
index_name VARCHAR(250)
,index_description VARCHAR(250)
,index_keys VARCHAR(250)
)
DECLARE @IndexInfo TABLE
(
table_name SYSNAME
,index_name VARCHAR(250)
,index_description VARCHAR(250)
,index_keys VARCHAR(250)
)
DECLARE @Tables TABLE
(
rowid INT NOT NULL IDENTITY(1, 1)
,tablename SYSNAME
)
DECLARE @MaxRow INT
DECLARE @CurrentRow INT
DECLARE @CurrentTable SYSNAME
INSERT INTO @Tables
SELECT DISTINCT t.name
FROM sys.indexes i
INNER JOIN sys.tables
t
ON i.object_id = t.object_id
WHERE i.name IS NOT NULL
SELECT @MaxRow = @@ROWCOUNT
,@CurrentRow = 1
WHILE @CurrentRow <= @MaxRow
BEGIN
SELECT @CurrentTable = tablename
FROM @Tables
WHERE rowid = @CurrentRow
INSERT INTO @IndexInfoTemp
EXEC Sp_helpindex
@CurrentTable
INSERT INTO @IndexInfo
(table_name
,index_name
,index_description
,index_keys)
SELECT @CurrentTable
,index_name
,index_description
,index_keys
FROM @IndexInfoTemp
DELETE FROM @IndexInfoTemp
SET @CurrentRow=@CurrentRow +
1
END --WHILE
SELECT table_name
,index_name
,index_description
,index_keys
,'ALTER TABLE
' + table_name
+ '
ADD CONSTRAINT pk_' +
table_name
+ '
PRIMARY KEY(' + index_keys + ');' AS [Create Primary Key]
FROM @IndexInfo
WHERE index_description LIKE 'clustered,
unique%'
AND table_name <> 'bi_params_transitionloading'
ORDER BY table_name;
