Locating a table in a database.

[SACRob]

I have recently taken over the administration of our microsoft great plains database. There has been allot of custom table creation and some additional undocumanted patches have been applied to our database. I need help finding a table that contains some regional informaton. I know the data that is currently saved in the table but i cannot find the table in the dbase. I was wondering if there is a way to search a database for some specific data in order to locate the table this data is stored in.

 

[RiverGuy]

There's no built in way that I know of. But you could build dynamic SQL statements based off the Tables and Columns in your database. Look at INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS for a starting point. It's not something that you want to let loose during peak hours however.

 

[Noonoo]

I may have completely misunderstood what you're after but if you want to list all column names (and data types) in a database you could run this:

SELECT o.name as TableName, Column_Name, C.Data_Type
FROM dbo.sysobjects o
INNER JOIN information_schema.columns C
ON o.Name = C.Table_Name
WHERE xtype = 'U'
ORDER BY Name

If there's some data but you don't know which table/column it's in you could always use this query to build a cursor, writing and excuting a SQL search string on each loop looking for the value you are after in the current column.

 

[Crowley16]

depending on the complexity of the system, I sometimes use sql profiler to do this sort of stuff.

basically log sprocs/sql statements with a filter for user/application should give either the sql statement(s) or sprocs being used. Then you just have to use the front end to interact with the data...

--------------------
Procrastinate Now!

 

[TysonLPrice]

I have this in my tool kit but haven't used it in a long time. It will probably run for quite a while depending on the size of your DB:

declare @SearchStr nvarchar(100)
set @SearchStr = 'serach string here'
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 (nolock)
			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 (Nolock)
				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 ColumnName, ColumnValue FROM #Results

 

[TysonLPrice]

Just for grins I ran this, since it's been so long, and it works.