September 26, 2003 at 10:34 am
query helpI have a table with following columns
table_name,index_name,key_seq,col_name
ahrq_ind,patind ,1,patkey
ahrq_ind,patind ,2,ahrq_ind
ahrq_ind,patkey ,1,patkey
ahrq_pop,admitted ,1,discharged
ahrq_pop,discharged,1,discharged
ahrq_pop,patkey ,1,patkey
ahrq_pop,patpop ,1,patkey
ahrq_pop,patpop ,2,ahrq_pop
algor ,aggr_indic,1,aggr_indic
algor ,agrp_indic,1,agrp_indic
algor ,cri_indic ,1,cri_indic
algor ,db_fld_ph ,1,db_name
algor ,db_fld_ph ,2,field_name
algor ,db_fld_ph ,3,qy_phase
I want to select from this table and be able to construct the following statement
create unique clustered index xpkpatind on ahrq_ind(patkey, ahrq_ind)
create unique clustered index xpkpatpop on ahrq_pop(pateky, ahrq_pop)
Any ideas on accomplishing this. THanks
September 26, 2003 at 11:01 am
CREATE FUNCTION dbo.udf_ListCols(@IndexName sysname)
RETURNS nvarchar(4000) AS BEGIN
DECLARE @List nvarchar(4000)
SELECT @List = ISNULL(@List + ', ','') + Col_Name
FROM Indices
WHERE Index_Name = @IndexName
ORDER BY Key_Seq
RETURN(@List) END
DECLARE @Index_Name sysname, @Table_Name sysname
DECLARE @sql varchar(8000)
DECLARE cur_idx CURSOR FOR
SELECT DISTINCT Table_Name, Index_Name FROM Indices
OPEN cur_idx
FETCH NEXT FROM cur_idx INTO @Table_Name, @Index_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'CREATE UNIQUE CLUSTERED INDEX xpk' + @Index_Name
+ ' ON ' + @Table_Name + '(' + dbo.udf_ListCols(@Index_Name) + ')'
PRINT @sql
FETCH NEXT FROM cur_idx INTO @Table_Name, @Index_Name
END
CLOSE cur_idx
DEALLOCATE cur_idx
--Jonathan
--Jonathan
September 26, 2003 at 11:27 am
Thanks Johnathan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply