Find value anywhere in database using one column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find value anywhere in database using one column

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-06 : 13:45:26

I have a medical database that stores appointments and many other details. I have to create a query that uses the appointment time and date, but I cannot find it anywhere in the database, but there is an application that uses it freely, so it is in there. I thought to query the entire database based on Patient_ID which is in many tables and look for the particular appointment times I see in the application. Then I know I have the right column. Can someon tell me how I can query all the tables in the database based on the Patient_ID column and look for that one value, say, 'P1001'?

Duane

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-08-06 : 14:12:31

You will need to use some sort of loop (i.e. cursor or while)



Declare @ObjectID int,@MyTable varchar(50)
set @ObjectID = 0
while exists (select * 
				from 
					sys.tables a 
				inner Join 
					sys.Columns b 
				on		a.Object_ID = b.Object_ID 
				where 
					type = 'u' 
				and object_ID > @ObjectID 
				and b.[Name] = 'Patient_ID' 
				)
BEGIN
				select top 1 @myTable = [Name],@ObjectID = a.ObjectID 	
				from 
					sys.tables a 
				inner Join 
					sys.Columns b 
				on		a.Object_ID = b.Object_ID 
				where 
					type = 'u' 
				and object_ID > @ObjectID 
				and b.[Name] = 'Patient_ID' 
				order by Object_ID

exec('Select ' + @MyTable + ' as TableName,* from ' + @MyTable + 'a where a.Patient_ID = 1234') --Change this to the ID you are looking for
END

Make sure to change the patient_ID from 1234 to whatever you are looking for.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-06 : 14:25:07

Thank you. I'll try that.

Duane

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-08-06 : 14:28:11

I copied this from somewhere, quite useful

Jim
BEGIN

DECLARE @searchStr varchar (20)
set @searchstr = 'Pdon;y'
/*
spFindStringValues 'prorate'
*/

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT Distinct ColumnName, ColumnValue FROM #Results

DROP TABLE #results

END

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-08-06 : 14:55:27

Thank you. I'll try that, too.

Duane

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-07 : 01:42:06

[code]declare @sql varchar(max),@search varchar(100)
set @sql=''
set @search='P1001'

select
@sql=@sql+'select '''+c.table_name+''' as table_name,'''+c.column_name+''' as column_name,['+c.column_name+'] as column_value from '+c.table_name+' where ['+c.column_name+'] like ''%'+@search+'%'' union all '
from
information_schema.columns as c inner join information_schema.tables as t on c.table_name=t.table_name
where
t.table_type='BASE TABLE'
and data_type like '%char%'

set @sql=left(@sql,LEN(@sql)-10)
exec(@sql)
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources