?
Solved

SQL 2005 Coding question

Posted on 2010-01-04
6
Medium Priority
?
278 Views
Last Modified: 2012-05-08
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"

0
Comment
Question by:Jeff S
6 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 1000 total points
ID: 26171505
select * from information_schema.columns
where column_name like '%fieldname%'
0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26171538

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
 
LVL 13

Assisted Solution

by:zadeveloper
zadeveloper earned 1000 total points
ID: 26171549
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

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

Open in new window

0
 
LVL 13

Expert Comment

by:zadeveloper
ID: 26171661
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
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 31672407
THANKS!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question