Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


share|improve this question
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
up vote 10 down vote accepted

Mmhh you could try:

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>',>=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>

     , TABLE_NAME     
WHERE COLUMN_NAME LIKE '%<string you are looking for>%'

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

share|improve this answer
USE <database>;

Declare @Col varchar(10);
Declare @Val varchar(10);
Declare @tablename varchar(20);
Declare @sql nvarchar(200);
Declare @sql1 nvarchar(200);

SET @Col = '...';   --INSERT COLUMN NAME

IF OBJECT_ID('tempdb.dbo.##TempTable1', 'U') IS NOT NULL
DROP TABLE ##TempTable1;

into ##TempTable1
FROM sysobjects
WHERE id IN ( SELECT id FROM syscolumns WHERE name = @Col )
ORDER BY name asc

set rowcount 1

update ##TempTable1 set mykey = 1

while @@rowcount > 0
    set rowcount 0

    set @tablename =  (select name from ##TempTable1 where mykey = 1)
    set @sql = 'If Exists (SELECT * FROM '+@tablename+' WHERE '+@Col+'='+@Val+') SELECT * FROM '+@tablename+' WHERE '+@Col+'='+@Val+';'
    set @sql1 = 'If Exists (SELECT * FROM '+@tablename+' WHERE '+@Col+'='+@Val+') select name from ##TempTable1 where mykey = 1'
    EXEC sp_executesql @sql1
    EXEC sp_executesql @sql

    delete ##TempTable1 where mykey = 1
        set rowcount 1
    update ##TempTable1 set mykey = 1
set rowcount 0
share|improve this answer

Your Answer


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.