Finding All Columns Named SSN

[bruce282]

I'm trying to find all occurrences of a column named SSN in all tables in all user databases. A perfect solution would be to also alter the format of SSN to CHAR (9).

Thanks,
Bruce

 

[gmmastros]

Try this:

Create Table #Code(Query VarChar(8000))

exec sp_msforeachdb '
    Insert Into #Code(Query)
    Select ''Alter Table ['' + Table_Catalog + ''].['' + Table_Schema + ''].['' + Table_Name + ''] Alter Column ['' + Column_Name + ''] Char(9) NULL'' 
    From   [?].Information_Schema.Columns 
    Where  Column_Name = ''SSN''
           And Data_Type <> ''char''
           And Character_Maximum_Length <> 9
    '

Select Query From #Code

Drop Table #Code

IF all goes well, you will get a result set that has the appropriate Alter Table statement for each occurrence of the SSN column in all of your databases. I STRONGLY encourage you to examine each statement closely BEFORE you copy/paste the code to a code window and run it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom