Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm using SQL server management studio 2008.

I have a huge legacy database with thousands of columns. It would be nice if I could search for all columns whose names contain a certain substring.

Does anyone know how to do this?

Thanks!

share|improve this question
1  
I do this kind of thing with my own batch file script, using a Java project called schemacrawler to do the work. –  djangofan Nov 1 '11 at 23:25

3 Answers 3

up vote 5 down vote accepted

Mmhh you could try:

select o.name,c.name 
from sys.columns c inner join sys.objects  o on c.object_id=o.object_id 
and o.type = 'U'
and CHARINDEX(<'your_sub_string>', c.name)>=1
share|improve this answer
    
ah, so there's no nice gui screen for this. –  IsaacB Nov 1 '11 at 23:18
    
Ok, what do you want exactly :) ? –  Stef Nov 1 '11 at 23:21
    
sys.columns doesn't seem to exist for me, i'm looking into it –  IsaacB Nov 1 '11 at 23:29
    
If you cannot "see" them it's because you don't have right permissions. Can you GRANT ? –  Stef Nov 1 '11 at 23:40
    
i was querying an old sql server 2000 database by accident from the 2008 studio. Your script works actually, thanks a lot. –  IsaacB Nov 1 '11 at 23:44

You could use some 3rd party tools like Red-Gate's SQL Search that are free.

share|improve this answer
    
i have something from red gate installed, maybe i'll check and see if I have sql search. Thanks! –  IsaacB Nov 2 '11 at 15:17

You can use the INFORMATION_SCHEMA views.

USE <database>

SELECT COLUMN_NAME
     , TABLE_NAME     
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%<string you are looking for>%'

One catch is to make sure to USE the correct database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.