11

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!

1
  • 1
    I do this kind of thing with my own batch file script, using a Java project called schemacrawler to do the work.
    – djangofan
    Commented Nov 1, 2011 at 23:25

4 Answers 4

16

Mmhh you could try:

use <your_database>
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
5
  • ah, so there's no nice gui screen for this.
    – IsaacB
    Commented Nov 1, 2011 at 23:18
  • Ok, what do you want exactly :) ?
    – Stef
    Commented Nov 1, 2011 at 23:21
  • sys.columns doesn't seem to exist for me, i'm looking into it
    – IsaacB
    Commented Nov 1, 2011 at 23:29
  • If you cannot "see" them it's because you don't have right permissions. Can you GRANT ?
    – Stef
    Commented Nov 1, 2011 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
    Commented Nov 1, 2011 at 23:44
2

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

1
  • i have something from red gate installed, maybe i'll check and see if I have sql search. Thanks!
    – IsaacB
    Commented Nov 2, 2011 at 15:17
2

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.

0
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
SET @Val = ...; -- INSERT COLUMN VALUE

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

SELECT NULL AS MYKEY, name
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
begin
    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
end
set rowcount 0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .