Displaying the List of Column Names in SQL Server

Two ways to display the list of column names for a table. The examples use the AdventureWorks database.

1. Use the sp_help procedure.
   

   exec sp_help 'HumanResources.Employee'

2. Query the information_schema table.

SELECT column_name
FROM information_schema.columns
WHERE table_name = 'HumanResources.Employee'
ORDER BY ordinal_position

Find All SQL Server Stored Procedures Containing Table Reference

This a real quick and dirty approach for locating all procedures in a SQL Server (2000/2005/2008?) database that contain a reference to a table name, or any other database object or string for that matter.

use [db name]
select distinct o.name
from syscomments c
inner join sysobjects o on o.id = c.id
where o.type = 'p'
and c.text like '% [name of db object or string] %'
--and o.name not like 'rpt_%'
order by o.name

In the 'where' clause above the sysobjects.type field is limited to 'p' for [stored] procedure. Another common type I have been searching in lately is a function 'fn'. Someone compiled a helpful list of sysobject types, comparing SQL Server 2000, 2005, and 2008 here.