Indexes
On SQL Server, the following SQL statement will determine the name of an index given its key columns:
select idx.name
from sysobjects tab
inner join sysindexes idx on (idx.id=tab.id and idx.name not like ''_WA_SYS%'')
where 0=(
select sum(1)-sum(case when col.name=need.name then 1 else 0 end)
from sysindexkeys ikey
inner join syscolumns col on (col.id=ikey.id and col.colid=ikey.colid and ikey.id=idx.id and ikey.indid=idx.indid)
full join (
select null keyno, null name where 1=2
union select 1, 'lineItemId'
union select 2, 'projectId'
) need on (need.keyno=ikey.keyno)
)
and tab.name='lineItem'Note the cryptic _WA_SYS reference. SQL Server creates indexes that start with that prefix to support statistics operations. It can happen in practice that there are two indexes on the same column set: one user index plus one system index. We need to ignore the latter.
Primary Keys
In a similar vein, here is a statement to identify a primary key structurally (note that column order is not relevant):
select pk.name
from sysobjects tab
inner join sysobjects pk on (pk.parent_obj=tab.id and pk.xtype='PK')
where 0=(
select sum(1)-sum(case when col.name=need.name then 1 else 0 end)
from sysindexes idx
inner join sysindexkeys ikey on (ikey.id=idx.id and ikey.indid=idx.indid)
inner join syscolumns col on (ikey.id=col.id and col.colid=ikey.colid)
full join (
select null name where 1=2
union select 'chartId'
union select 'rowId'
union select 'chartIndex'
) need on (need.name=col.name)
where idx.name=pk.name
)
and tab.name='tbCustomChartDataSet'Foreign Keys
To find a foreign key:
select fk.name
from sysobjects tab
inner join sysobjects fk on (fk.parent_obj=tab.id and fk.xtype='F')
where 0=(
select sum(1)-sum(case when col.name=need.name and rcol.name=need.rname then 1 else 0 end)
from sysforeignkeys con
inner join sysobjects rtab on (rtab.id=con.rkeyid)
inner join syscolumns col on (col.id=con.fkeyid and col.colid=con.fkey)
inner join syscolumns rcol on (rcol.id=con.rkeyid and rcol.colid=con.rkey)
full join (
select null name, null rname where 1=2
union select 'projectId', 'projectId'
union select 'projectStageId', 'projectStageId'
) need on (need.name=col.name and need.rname=rcol.name)
where con.constid=fk.id
and rtab.name='tbProjectStageXref'
)
and tab.name='lineItem'Column Constraints
To find a column constraint of some type (a 'check' constraint in the example):
select def.name from sysobjects def
inner join sysobjects tab on (tab.id=def.parent_obj)
inner join sysconstraints con on (con.id=tab.id and constid=def.id)
inner join syscolumns col on (col.id=tab.id and col.colid=con.colid)
where def.xtype='C'
and tab.name='unitConvVolume'
and col.name='qty_2'
Unique Constraints
To find a column constraint of some type (a 'check' constraint in the example):
select idx.name
from sysobjects tab
inner join sysobjects con on (con.parent_obj=tab.id)
inner join sysindexes idx on (idx.name=con.name)
where 0=(
select sum(1)-sum(case when col.name=need.name then 1 else 0 end)
from sysindexkeys ikey
inner join syscolumns col on (col.id=ikey.id and col.colid=ikey.colid and ikey.id=idx.id and ikey.indid=idx.indid)
full join (
select null keyno, null name where 1=2
union select 1, 'budgetId'
union select 2, 'afeName'
) need on (need.keyno=ikey.keyno)
)
and tab.name='tbAfe'Script Generation
Even more exciting, here is an SQL statement that generates an SQL script that can be run in another database to rename all indexes to match the names used in the first database (only indexes on user tables and that are not associated with a constraint or 'text' column are considered).
select text from (
select null tab, null idx, null seq, null text where 1=0
union select tab.name
, idx.name
, blk.seq
, replace(replace(blk.text, '$TABLE$', tab.name), '$INDEX$', idx.name) text
from sysindexes idx
inner join sysobjects tab on (tab.id=idx.id)
cross join (
select null seq, null text where 1=0
union select 1, 'declare @idx_name sysname'
union select 2, 'select @idx_name=tab.name+''.''+idx.name '
union select 3, 'from sysobjects tab '
union select 4, 'inner join sysindexes idx on (idx.id=tab.id and idx.name not like ''_WA_SYS%'') '
union select 5, 'where 0=( '
union select 6, ' select sum(1)-sum(case when col.name=need.name then 1 else 0 end) '
union select 7, ' from sysindexkeys ikey '
union select 8, ' inner join syscolumns col on (col.id=ikey.id and col.colid=ikey.colid and ikey.id=idx.id and ikey.indid=idx.indid) '
union select 9, ' full join ( '
union select 10, ' select null keyno, null name where 1=2 '
/* 1000-9999 reserved for key column names */
union select 10000, ' ) need on (need.keyno=ikey.keyno) '
union select 10001, ') '
union select 10002, 'and tab.name=''$TABLE$'' '
union select 10003, 'exec sp_rename @idx_name, ''$INDEX$'', ''INDEX'''
union select 10004, 'go'
union select 10005, ''
) blk
where tab.xtype='U'
and idx.indid<>255
and idx.name not like '_WA_SYS%'
and not exists (select 1 from sysobjects where name=idx.name)
union select tab.name
, idx.name
, 1000+ikey.keyno
, ' union select '+cast(ikey.keyno as varchar)+', '''+col.name+''''
from sysindexes idx
inner join sysobjects tab on (tab.id=idx.id)
inner join sysindexkeys ikey on (ikey.id=idx.id and ikey.indid=idx.indid)
inner join syscolumns col on (col.id=idx.id and col.colid=ikey.colid)
where tab.xtype='U'
and idx.indid<>255
and idx.name not like '_WA_SYS%'
and not exists (select 1 from sysobjects where name=idx.name)
) data
order by tab, idx, seq
