how do I find the the location of data
-
Question. Is there a way to search an entire database for a specific column and table?
example. I have a class code of 312564777, yet I do not know the table or column
where it is found. thank you for your time.
BC
-
This is how I do it FROM VB 6.
Private Sub SearchAllTextColumns(ByVal sDbName As String, ByVal sFind As String)
On Error GoTo Gestion:
Dim MyRsCols As ADODB.Recordset
Dim MyRsCount As ADODB.Recordset
Dim OColumn As ADODB.Field
Dim OTable As ADODB.Field
Dim oField As ADODB.Field
Dim sTable As String
Dim sColumns As String
Dim sSQL As String
Dim iCount As Integer
Dim bTableIsDone As Boolean
Dim sWhereCond As String
Set MyRsCols = New ADODB.Recordset
Set MyRsCount = New ADODB.Recordset
'This is a global variable
MyCn.Open
MyCn.Execute "USE " & sDbName
MyRsCols.Open "SELECT '[' + REPLACE(O.Name, '''', '''''') + ']' AS TblName, '[' + C.Name + ']' AS Colname FROM dbo.SysObjects O INNER JOIN dbo.SysColumns C on O.id = C.id WHERE O.Status >= 0 AND O.XType = 'U' AND C.XType IN (SELECT XType FROM dbo.SysTypes WHERE Collationid IS NOT NULL) ORDER BY TblName, ColName", MyCn, 1, 3
If Not MyRsCols.EOF Then
Set OTable = MyRsCols.Fields("TblName")
Set OColumn = MyRsCols.Fields("ColName")
While Not MyRsCols.EOF
sTable = OTable.Value
bTableIsDone = False
sColumns = ""
sWhereCond = " WHERE "
While Not bTableIsDone
sColumns = sColumns & OColumn.Value & ", "
sWhereCond = sWhereCond & OColumn.Value & " LIKE '" & sFind & "' OR "
MyRsCols.MoveNext
If MyRsCols.EOF Then
bTableIsDone = True
Else
bTableIsDone = OTable.Value <> sTable
End If
Wend
sColumns = left(sColumns, Len(sColumns) - 2)
sWhereCond = left(sWhereCond, Len(sWhereCond) - 4)
sSQL = "Select '" & sTable & "' AS TblName, * FROM dbo." & sTable & sWhereCond
Debug.Print sSQL
Wend
End If
MyRsCols.Close
MyCn.Close
Set MyRsCount = Nothing
Set MyRsCols = Nothing
Exit Sub
Gestion:
Select Case Err.Number
Case Else
Select Case ErrHandler(ModuleName, "ModGlobals", "SearchAllTextColumns", Err, Erl())
Case ErrResume
Resume
Case ErrResumeNext
Resume Next
Case ErrExit
MsgBox Err.Description & " : " & Err.Number
Exit Sub
End Select
End Select
End Sub
Private Sub RunSearch()
SearchAllTextColumns "Ideal", "062360REV8"
End Sub
Once you run this code you just copy / paste the debug pane to QA and run all the queries. FROM there you can see which queries give results. You could also do that in this script too but that's just a matter of preferences I guess!
-
Please not that this search only for columns which contain text. You'll have to change the first query (systypes table) to allow to search for number columns..
-
this is in an SQL data base. is there a general way to find either
number or text?
-
None that I know off. I already did this in a dts but it was basically the same code as above. This code basically writes all the select statements you need to find the value you are searching for. The only thing left to do is run the statement. I find this to be a reasonable trade off considering that this will never go into production (hopefully anyways).
-
OK I copied the code and I will play around with it.
thanks for your help.
BC
-
Here's the first correction for the XType part :
SELECT XType, * FROM dbo.SysTypes
WHERE Collationid IS NULL AND XType NOT IN (173, 61, 34, 58, 98, 189, 36, 165)
Then you'll jsut have to worry about the concatenation part... Shouldn't be too hard at this point.
-
This should work as well:
set nocount on
declare @results table ([ID] INT identity (1,1) not null, colname varchar(500), tablename varchar(500))
insert into @results
select sc.name, so.name from syscolumns sc inner join sysobjects so on sc.id = so.id where so.xtype = 'U'
declare @sqlstring varchar(1000)
declare @searchstring varchar(500)
declare @loopmin int
declare @loopmax int
--this is where you set your search criteria
set @searchstring = 'searchvalue'
select @loopmin = min([id]) from @results
select @loopmax = max([id]) from @results
while @loopmin 0 print @sqlstring
set @loopmin = @loopmin + 1
END
-
This works well. I found it a while back. Just create the sproc by running the code below. Use it by running exec SearchAllTable 'textToFind'
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--Here is the complete stored procedure code:
ALTER PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
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 ColumnName, ColumnValue FROM #Results
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
If you're using sql 2000 there is something called Object Search that will let you search for objects of a certain type by name. Not sure if this feature is included in 2005.
-
The problem with this solution is that you will do a table / index scan for every column in each table. Here I have quite a few tables with over 100K rows and over 100 columns of each datatype (numeric or string). I cannot afford to do 100 or 200 scans on each of those tables. That is why my code creates the full select statement and runs it only once per table.
-
This solution is similar to the other one. Only 1 column is scanned per run. If this is acceptable in your situation then go with it, it'll work. But if like me you need to run this on a production server once in a while (takes much less time than backup / restore / then search on test server >> sometimes I need an answer right away) or if you just have a huge database, then I strongly suggest you tweak my code to fit your needs. Also my code is using vb 6 (works in VBA too) which means that the query string can go over the 8K limit of sql server... which is also a requirement in my environement.
-
That's a very good point, however we need to find a specific value in the whole database. Sometime that this tool is not built to do
! -
This will produce the queries to find all occurrences of 312564777 in int or bigint columns, or [n][var]char columns of length 9 or greater, in all user tables with only one scan per table. It will also work in SQL 2000 if you change the system table references in the INSERT/SELECT query. You could limit the datatype list if you think you know the datatype, or expand it by adding decimal, numeric, or money if you want to cover all possibilities.
SET
NOCOUNT ON
CREATE TABLE #ToSearch (
TableName varchar(250),
ColumnName varchar(100),
StringCompare bit,
PRIMARY KEY CLUSTERED (TableName, ColumnName))
INSERT INTO #ToSearch
SELECT quotename(s.name) + '.' + quotename(t.name) AS TableName, quotename(c.name) AS ColumnName,
CASE WHEN c.collation_name IS NULL THEN 0 ELSE 1 END AS StringCompare
FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.types x on x.system_type_id = c.system_type_id and x.user_type_id = c.user_type_id
WHERE x.name IN ('int', 'bigint')
OR (x.name IN ('char', 'varchar') AND c.max_length >= 9)
OR (x.name IN ('nchar', 'nvarchar') AND c.max_length >= 18)
ORDER BY TableName, ColumnName
DECLARE @tbl varchar(500), @where varchar(8000)
DECLARE tbls CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT TableName FROM #ToSearch
OPEN tbls
WHILE 1=1 BEGIN
FETCH next FROM tbls INTO @tbl
IF @@FETCH_STATUS <> 0 BREAK
SET @where = NULL
SELECT @where = ISNULL(@where + ' OR ', '') + ColumnName + '=' + CASE StringCompare WHEN 1 THEN '''312564777''' ELSE '312564777' END
FROM #ToSearch WHERE TableName = @tbl
PRINT 'SELECT * FROM ' + @tbl + ' WHERE ' + @where
END
CLOSE tbls
DEALLOCATE tbls
DROP TABLE #ToSearch
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply