Searching through a DB 1

[OldSkoolSkater]

Hi all

I have very limited T-SQL knowledge and need to find out the following:
I need to find a value in a SQL DB and it's not in the table that I thought it would be. Is there a way of searching through all the tables in the DB to find this value?
If there is, could you spell it out in T-SQL for me?

Using SQL 2000 by the way.

Cheers

Steve - Network Coordinating in the UK

 

[JayKusch]

If you are trying to find a particular "column name" that is not in the table you though it should be in BUT may be in another table, use this code ...

SELECT * FROM Information_Schema.Columns where Column_Name LIKE '%ColumnName%'

Of course you need to change the ColumnName field to the desired one.

However, if you are looking for a "value" you thought was in a column BUT may be located in another column of another table... create this SP and execute it in Query Analyzer

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE FindValueInAllTables
(
	@FindValue nvarchar(100)
)
AS
BEGIN

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

	SET NOCOUNT ON

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

	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 #Found
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @FindValue2
				)
			END
		END	
	END

	SELECT ColumnName, ColumnValue FROM #Found
END
[code] 
Then execute it in QA by running ...

EXEC FindValueInAllTables '123ABC' - Supply your value for '123ABC'

Thanks

J. Kusch

 

[OldSkoolSkater]

Wow!

Not something I would have thought up myself!!
My situation is similar to your latter scenario, but not only do I not know which table the value is in, but also which column....

I'll give your script a go and see what happens.

Cheers

Steve - Network Coordinating in the UK

 

[JayKusch]

That script should cover both scenerios. Enjoy!

Thanks

J. Kusch

 

[OldSkoolSkater]

The script worked a treat Jay!

Also taught me how to create Stored procedures as a bonus!

Cheers

Steve - Network Coordinating in the UK