SQL 2005 Coding question

How can I scan all my tables against my db and find the ones that contain a specific fieldname? I want to find all instances were I have a field name "PreviousPatientProfileId"

LVL 7
Jeff SAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
select * from information_schema.columns
where column_name like '%fieldname%'
0
 
zadeveloperCommented:

select 
	t.name
from 
	sysobjects t
	inner join sys.columns c on c.object_id = t.id
where
	c.name like ('%PreviousPatientProfileId%')

Open in new window

0
 
zadeveloperConnect With a Mentor Commented:
sorry, add the xtype to be "more" correct
select 
	t.name
from 
	sysobjects t
	inner join sys.columns c on c.object_id = t.id
where
	c.name like ('%PreviousPatientProfileId%')
	and t.xtype = 'u'

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
sameer2010Commented:
Use this
USE <YOUR DATABASE>;
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE
COLUMN_NAME = 'PreviousPatientProfileId';

Open in new window

0
 
zadeveloperCommented:
I would Also suggest:
(This will search all your stored procs to see if you are using the field there as well

declare @Text nvarchar(100)
set @Text = '%PreviousPatientProfileId%'

SELECT OBJECT_NAME(id) 
    FROM syscomments 
    WHERE [text] LIKE @Text
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

Open in new window

0
 
Jeff SAuthor Commented:
THANKS!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.