Search for column name 1

[TomR100]

Hello again,

Is there any way to search the tables for a column name besides opening each table properties?

Thanks

 

[cmmrfrds]

Modify this as necessary.

select SO.Id,
TableName = SO.Name,
ColumnName = SC.name
from Sysobjects SO
Inner join Syscolumns SC on SO.id = SC.id
where SO.TYpe = 'U'

 

[tlbroadbent]

In SQL 7 and higher you can use the Information_Schema views in each database. Run the following query in the database to list the columns for each table as well as properties of the columns.

Select *
From information_schema.columns

Columns in a specific table:

Select *
From information_schema.columns
Where table_name='AcctRecv' Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[TomR100]

Hello again,

Is there a way to search Stored Procedures to find out how a table is populated with certain criteria. I don't really want to open every proc to find out if it matchs the information I need.

Thank you again
TomR100

 

[cathyw]

There is probably a more elegant way to do this, but here's one way you can search your stored procedures:
1. generate a script containing all of your stored procedures
2. open the script in any text editor
3. use the find capabilities of the text editor to search for the table name or whatever criteria works best to find what you're looking for

Hope this helps,
Cathy

 

[TomR100]

Thank you Cathy. I will try this.

 

[tlbroadbent]

If your stored procedures are not encypted, you can use search the syscomments table for a character string.

Select o.name
from syscomments c
join sysobjects o
on c.id=o.id
where o.type='p'
and charindex('the text you want to find', c.text)>0 If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm