Searching particular Column name in all the tables in the database

Posted Date: 20 Mar 2007 Resource Type: Code Snippets Category: SQL DBA

Posted By: janaki Member Level: Gold
Rating:

Points: 10

The below mentioned query will search particular column name in all the tables in the database. The fields in the query include TABLE_NAME & ORDINAL_POSITION. Fist field represents the table name where the column name occurs and the second represents the row position. For example if the particular column occurs as third field in the table, then ORDINAL_POSITION will yield 3.

To achieve this

1. Select / Give the desired database where you wish to search the column name.
2. In the query give the column to be searched in COLUMN_NAME.
3. Execute the Query in the query analyzer.


[use databasename]

SELECT TABLE_NAME,ORDINAL_POSITION  FROM INFORMATION_SCHEMA.COLUMNS 
WHERE  COLUMN_NAME='Column to be searched'

Responses

Author: K.Rajapandian 24 Apr 2007 Member Level: Bronze Points : 0

Hi Friends,

Try the below Query also.

SELECT SI.NAME TABLENAME FROM SYSINDEXES SI, SYSCOLUMNS SC WHERE SI.ID=SC.ID AND SC.NAME='COLUMN NAME'

Thanks
K. Rajapandian

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.


	Previous Resource: How to list out database tables which have no records in SQL Server
	Return to Discussion Resource Index
	Post New Resource
	Category: SQL DBA

Post resources and earn money!

Related Resources

dotNet Slackers BizTalk Adaptors Web Design

it support