Epic Puck

I have run into this.  I am working against a database with millions of rows and possibly hundreds of tables.  How can I find out if I have used that column before and quickly?

to find a column name from all columns in a 2008 (R2) database:

SELECT distinct name
FROM sys.all_columns
where name like '%column name%'

to display all of the tables as well as their row counts from a 2008 (r2) database:

select distinct convert(varchar(30),object_name(a.id)) [Table Name], a.rows
from sysindexes a inner join sysobjects b on a.id = b.id
inner join INFORMATION_SCHEMA.TABLES c on c.TABLE_NAME=convert(varchar(30),object_name(a.id))
where c.TABLE_CATALOG='database name'

I hope that these help you like they helped me.

Be Sociable, Share!

•