URGENT! - Script to get Table and Column names based on parameters

Question

URGENT! - Script to get Table and Column names based on parameters

kwflanagan

Old Hand

Points: 355
More actions
April 14, 2004 at 12:56 pm

#163542

I need a script/sp/function that will check through all the tables in a SQL Server database instance (multiple databases) and return the Table and Column name for all columns where it is a varchar and the length is 30 or over. Ideally, it would be even better if the script could pull out all the data that currently existed in those columns where it was an email address.

Thanks

Kevin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

This will get you some of the way - it works in the current database, so you'd need to iterate round all the dbs you need to check.

SELECT table_catalog, table_name, column_name, character_maximum_length

FROM Information_Schema.Columns

where data_type = 'varchar' and character_maximum_length >= 30

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Chuck Ritenour SSC-Addicted Points: 420 More actions · Answer 2

this will get you the entire way - change the # tables to non-temp if you want. I've tested it across 245 databases, worked for me in 7 seconds with 37,500 rows as result.

Just slap this in QA and hit go:

Create table #DB_Name (dbname varchar (100))

Create table #results (dbname varchar (100), tabName varchar (100), colName varchar (100))

Insert into #DB_Name(dbname)

select [name] from master.dbo.sysdatabases where dbid > 6

DECLARE @dbName varchar (100), @sql varchar (1000)

set @dbName = ''

DECLARE db_Update CURSOR

FOR

SELECT dbname from #DB_name

OPEN db_Update

FETCH NEXT FROM db_Update INTO @dbName

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

set @sql = 'use ['+@dbname+ '] '

set @sql = @sql +' Insert into #results(dbName, TabName, colName) SELECT '

set @sql = @sql + ''''+@dbname+''''+ ' as dbName,table_name, column_name

FROM Information_Schema.Columns

where data_type = ''varchar'' and character_maximum_length >= 30'

Exec (@sql)

END

FETCH NEXT FROM db_Update INTO @dbName

END

CLOSE db_Update

DEALLOCATE db_Update

Select * from #results

drop table #db_name

drop table #results

Chuck

Thanks, and don't forget to Chuckle

Chuck Ritenour SSC-Addicted Points: 420 More actions · Answer 3

For the email address part - another cursor query has to be written to run through the result set, each database, each table and column looking for '%_.@_._%' or whatever you determine will indicate it is a email address. I've given you the tools to write that in my last - let's see if you can modify it to do this.

There are other ways that won't need a cursor, but why spend 2 days writing a stored proc that saves 2 seconds off the time?

Thanks, and don't forget to Chuckle

kwflanagan Old Hand Points: 355 More actions · Answer 4

Thanks guys. These both worked great. I appreciate your fast response as well.