I have a field name used in a SQL Server DB - how can I find where it is used?

I have a field na开发者_高级运维me used in a SQL Server DB - I do not know if it is a table field or a stored procedure parameter. Is there a query I can run against the DB to find candidate fields in tables and stored procedures?

Depends on which version of sql server you are using:

For 2000, use syscolumns:

select object_name(select object_name(id)
from syscolumns
where name = 'ID'

For 2005+ use sys.columns:

select object_name(object_id)
from sys.columns
where name = 'ID'

The use of the object_name() function negates the need for an inner join on the objects table.

If it's SQL Server 2005 or 2008 you can use the INFORMATION_SCHEMA views.

http://msdn.microsoft.com/en-us/library/ms186778.aspx

SQL statement like this will find what you're looking for:

select * from information_schema.parameters where parameter_name like '%field%'
select * from information_schema.columns where column_name like '%field%'

Yes you can:

select o.type, o.name from sys.columns c
    join sys.objects o on c.object_id = o.object_id
where c.name = @col_to_find

Or you can install the free Red Gate Search add-in.