July 9, 2008 at 10:43 pm
How to identify the values against same field in all tables in database ?
I have a field EmployeeID. This field is there in a good number of tables connected to parent table with a foreign key reference.
I want to find out the value in this field in all tables in the database..
Or
I want to see if any data is there in this field in any of the tables.
Please help
July 9, 2008 at 11:21 pm
Hi Sanuj,
First of all check for the tables which has field EmployeeID:
SELECT O.Name TableName,C.Name ColumnName From sys.Objects O
INNER JOIN sys.Columns C ON C.OBJECT_ID=O.OBJECT_ID
WHERE C.NAME LIKE 'EmployeeID' AND type_desc='USER_TABLE'
Now you can check the values in these tables.
July 11, 2008 at 6:45 am
thanks Hari
I didn't post it (yet) but needed a way to find all fields called xxx
in the database
the system was designed by one programmer and he was highly disciplined in his naming convention
July 15, 2008 at 10:01 pm
Thanks Hari.. Thank you very much 🙂
July 16, 2008 at 12:42 am
Hi Sanuj,
here is how I handle this:
-- =============================================
-- Description:list all Fields of all tables of all databases with name like '%searchfor%%' auf
-- =============================================
Create PROCEDURE [dbo].[list_fields]
@searchfor varchar(255)
AS
BEGIN
SET NOCOUNT ON;
declare @database varchar(100)
declare @table varchar(100)
declare @sql varchar(1000)
-- temp. table
CREATE table #tmp (
datenbase_name varchar(100),
table_name varchar(100),
field_name varchar (100),
fieldtype varchar (100),
tabletype varchar (100)
)
-- loop for all databases
declare cur cursor for
select name from
Master.sys.databases
-- maybe you want to exclude some databases
-- where name not in ('master', 'modell', 'msdb','tempdb','AdventureWorks', 'AdventureWorksDW', 'Berichtsserver', 'BerichtsserverTempDB', 'ProductCockpitReportServer', 'ProductCockpitReportServerTempDB')
OPEN cur
FETCH NEXT FROM cur
INTO @database
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='insert into #tmp(datenbase_name, table_name, field_name, fieldtype, tabletype)'
set @sql=@sql+' select C.TABLE_CATALOG, C.TABLE_NAME, COLUMN_NAME, DATA_TYPE, TABLE_TYPE from ' + @database + '.INFORMATION_SCHEMA.COLUMNS C'
set @sql=@sql+' inner join ' + @database + '.INFORMATION_SCHEMA.TABLES T on C.TABLE_NAME=T.TABLE_NAME'
set @sql=@sql+' where COLUMN_NAME like ''%' + @searchfor + '%'''
execute (@sql)
FETCH NEXT FROM cur
INTO @database
END
CLOSE cur
DEALLOCATE cur
select * from #tmp order by datenbase_name, table_name, field_name
DROP table #tmp
/*
exec list_fields 'EmployeeID'
*/
END
Regards
Smerg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply