SQL tables

Question

SQL tables

alan.nichol

Old Hand

Points: 329
More actions
January 22, 2010 at 4:33 am

#137600

Dear serverCentral
How can I find a particular column in a number of tables.
For example, I have column name , grporder and I want to know which one of the 100 tables it is in...is there a quick way of finding that out?
There is probably something very simple, but I can't see it at the moment

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Query the sys.columns view. Join it to sys.tables for the table name, or use the object_name function.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi ,

Just an addition to the above method, also I hope you know the database name among the existing the databases in the server, if so you can even use the below script in that database.

SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'C1'

If you dont know the databasename you have to itrate through each database in the server to check the table which has the your column. In that case you can use the below script.

EXEC master.sys.SP_MSFOREACHDB 'USE ?; IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ''C1'') SELECT ''?'' as db_name,table_name as table_name,column_name as column_name from information_schema.columns WHERE COLUMN_NAME = ''C1'''

In the above script just replace C1 with your column name

Regards,

MC

Thanks & Regards,
MC

Jeff Moden SSC Guru Points: 1003921 More actions · Answer 3

As a side bar... That's one of the things I miss about SQL Server 2000 and Query Analyzer where you could just press {f4} for such a thing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

alpeshgediya Old Hand Points: 321 More actions · Answer 4

Use below query ..

SELECT *

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = (Your column name..)

Hope this will help you..

cobrien 37995 Grasshopper Points: 11 More actions · Answer 5

Got this somewhere (sorry forgot where) and I use instead of missing "object search" in SQL 2005

Change the "where" clause to limit to object types you want to search

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'dog', @notcontain = ''

SELECT DISTINCT left(sysobjects.name,60) AS [Object Name] ,

case when sysobjects.xtype = 'P' then 'Stored Proc'

when sysobjects.xtype = 'TF' then 'Function'

when sysobjects.xtype = 'TR' then 'Trigger'

when sysobjects.xtype = 'U' then 'User table'

when sysobjects.xtype = 'V' then 'View'

end as [Object Type]

FROM sysobjects

LEFT JOIN syscomments ON sysobjects.id = syscomments.id

LEFT JOIN syscolumns ON sysobjects.id = syscolumns.id

--WHERE sysobjects.type in ('P','TF','TR','U','V')

WHERE sysobjects.type = 'U'

AND ((CHARINDEX(@SEARCHSTRING,syscomments.text)>0

AND ((CHARINDEX(@notcontain,syscomments.text)=0

or CHARINDEX(@notcontain,syscomments.text)<>0)) )

OR

CHARINDEX(@SEARCHSTRING,syscolumns.name)>0

)

ORDER BY [Object Type], [Object Name]

Lowell SSC Guru Points: 323507 More actions · Answer 6

here's another script that i use a lot;

i stick it in master, and then in any database i might type "sp_find order" in any database;

and it returns all tables(or views) that contain the string first, and then all the column names along with their table/view name that contain the string in the column name;

CREATE procedure [dbo].[sp_find]

@findcolumn varchar(50)

AS

BEGIN

SET NOCOUNT ON

SELECT

TableFound,

ColumnFound

FROM

(

SELECT

1 AS SortOrder,

sysobjects.name AS TableFound,

'' AS ColumnFound

FROM sysobjects

WHERE sysobjects.xtype IN('U' ,'V')

AND sysobjects.name LIKE '%' + @findcolumn + '%'

UNION ALL

SELECT

2 AS SortOrder,

sysobjects.name AS TableFound,

syscolumns.name AS ColumnFound

FROM sysobjects

INNER JOIN syscolumns ON sysobjects.id=syscolumns.id

WHERE sysobjects.xtype IN('U' ,'V')

AND syscolumns.name like '%' + @findcolumn + '%'

) X

ORDER BY

SortOrder,

TableFound,

ColumnFound

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!