Finding all occurenses of a field in a databaes
-
HI. Can someone tell me of an easy way to locate all the occurences of a particular field name in all tables of a particular database? For example: I'm looking for the field named 'first_name'. I want to know all tables that this field resides in a particular database.
Thanks,
Juanita
-
Try:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'first_name'
or with wildcards
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%first%'
will find every occurence of the word "first" in the database if you are set to a case insensitive language.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
THANK YOU SO MUCH !!
That is exactly what I needed !!
Juanita
-
Just take all of the joy out of my life!
And here I've been developing this query
CREATE view dbo.vzTableColumnNames as
select o.name as TableName, c.name as ColumnName, c.ColID,
t.name + '(' + rtrim(cast(c.Length as char(3))) +
case when c.xscale > 0 then
-- '[' + rtrim(cast(c.xprec as char(2))) + --xprec = c.length for numeric data types
',' + rtrim(cast(c.xscale as char(2))) + ')' else ')' end as ColType,
case c.Isnullable when 1 then 'null' else 'not null' end as IsNullable,
case when d.text is null then '' else ('DEFAULT ' + d.text) end as iDefault
, FieldCount
from syscolumns c --field names
inner join sysobjects o --table names
on c.id = o.id
inner join systypes t --data type names
on c.xtype = t.xtype
left join syscomments d --defaults
on c.cdefault = d.id
inner join (select [id], count(*) FieldCount
from syscolumns group by [id]) fc
on o.id = fc.id
where o.xtype = 'U' --user tables
--order by o.name, c.colid
to provide largely the same information, and you just reduce it to a single statement. Swine. 🙂
I've always enjoyed querying the base tables, and I've never taken the time to play with the schema views, so thanks, Jim, for your reply. I'm looking forward to studying it further.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
What I've actually have done on some of my servers is edit the model to add the put the view
Create View IS.COLS
as
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
So I can get to it even quicker.
I haven't ever used anything but the supplied tools that come with SQL Server. I haven't found I really need them. 
Well, 
really, my boss doesn't want to cough up the extra $$$ 
so I have to do without. 
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply