to find name of indexes by name of fields

• Linata

  Ten Centuries

  Points: 1370

  More actions

  July 27, 2006 at 5:56 am

  #61585

  I need to change type of many fields.

  It can't be done without drop all constraints and indexes.

  It's not difficulties with constraints.

  How can I build automaticaly sentences  "drop index ...", "create index ..."

  when I know only names of fields to change .

   

  Thanks in advance.

• David.Poole

  SSC Guru

  Points: 76001

  More actions

  July 27, 2006 at 6:40 am

  #651853

  select

  object_name(i.id) AS TableName,

  c.name as FieldName,

  I.Name AS IndexName ,

  objectproperty(object_id(i.name),'IsPrimarykey') AS IsPrimaryKey

  from sysindexkeys as ik

  inner join sysindexes as i

  on ik.id = i.id

  and ik.indid = i.indid

  inner join syscolumns as c

  on ik.id = c.id

  and ik.colid=c.colid

  where objectproperty(i.id,'IsUserTable')=1

  AND c.name = @YourFieldName

  LinkedIn Profile

• Linata

  Ten Centuries

  Points: 1370

  More actions

  July 27, 2006 at 7:43 am

  #651875

  It works great.

  Thank you very much, David.