January 22, 2008 at 8:33 am
How can I find a table and column name from a DB If i was given some sample data.
let say I have a data 'Employee Credit' in some field, how can i find in which field it is and in which table from searching the whole DB
January 22, 2008 at 8:58 am
There's no good way to find data other than selecting from every column and table. There are no functions to "search" a database.
If you have some idea of the field, you could check information_schema.columns for the field name.
January 22, 2008 at 8:59 am
yeah i nkow how to check if i have some idea about the column, i just have some piece of date and need find where it is stored.
January 22, 2008 at 9:31 am
The sample data you have been given. Is it an exact match (=) or partial (like).
Also what is the size of the database you want to search?
January 22, 2008 at 9:36 am
sample data is exact like.the size of the DB wud be 900MB
If its not possible, i wud like to know how can i can find a table which is updated recently.
I wud like to enter some data from the app and check which table is updated.
thanks
January 22, 2008 at 10:02 am
Try the below...
This will take a while as it searches every char, nchar, nvarchar & varchar column.
-------------
declare @cmd nvarchar(4000)
declare @srchstr nvarchar(200)
declare @tbl nvarchar(255)
declare @col nvarchar(255)
create table ##results (res varchar(255))
select @srchstr='ALFKI' --CHANGE THIS TO YOUR SEARCH STRING
declare structure cursor for
select table_name,column_name from information_schema.columns
where data_type like '%char%'--YOU WIDEN THE RANGE OF DATA TYPES SEARCHED BY CHANGING THIS
open structure
fetch next from structure into @tbl,@col
while @@fetch_status=0
begin
select @cmd='if (select count(*) from ['+@tbl+'] with (nolock) where ['+@col+']='''+@srchstr+''')>0
begin
insert into ##results values(''Data Found in table ['+@tbl+'] column ['+@col+']'')
end'
print @cmd
exec sp_executesql @cmd
fetch next from structure into @tbl,@col
end
select * from ##results
drop table ##results
close structure
deallocate structure
January 22, 2008 at 10:22 am
when in ran that query i gives me an error saying invalid object name for most of the tables though the table name is correct
January 22, 2008 at 10:32 am
It could also be the column.
It prints the command in the messages window try copying one of the erroring strings to the query and see what happens.
January 22, 2008 at 11:01 am
tried with some other sample date where i know the table and column name but its not giving nay result in the temp table.
January 22, 2008 at 11:03 am
is it nchar,char,nvarchar or varchar value?
January 22, 2008 at 11:05 am
its a char value
January 22, 2008 at 11:11 am
if you were to write the sql statement yourself with a where clause would you use a like or =
January 22, 2008 at 11:12 am
if this is not getting result,is there a way to find a table or column name which was recently updated or recently inserted a row
thanks
January 22, 2008 at 11:16 am
Not sure.
January 22, 2008 at 11:23 am
Similar to what someone else posted, this procedure "UGLYSEARCH" is not for use on a production box...if you have a database with 50K columns that are varchar, etc, this is going to run 50K SELECT statements...which might interfere with production databases' availability:
usage is simply EXEC UGLYSEARCH 'Employee Credit'
CREATE PROCEDURE UGLYSEARCH
-- EXEC UGLYSEARCH 'Employee Credit'
@SEARCHSTRING VARCHAR(50)
AS
SET NOCOUNT ON
DECLARE @sql VARCHAR(500),
@TABLENAME VARCHAR(60),
@COLUMNNAME VARCHAR(60)
CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600))
SELECT
SYSOBJECTS.NAME AS TBLNAME,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR')
ORDER BY TBLNAME,COLNAME
DECLARE C1 CURSOR FOR
SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME
OPEN C1
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @sql = 'IF EXISTS(SELECT * FROM '
+ @TABLENAME + ' WHERE '
+ @COLUMNNAME + ' LIKE ''%' + @SEARCHSTRING + '%'')
INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL)
VALUES(''' + @TABLENAME + ''',''' + @COLUMNNAME + ''','' SELECT * FROM '
+ @TABLENAME + ' WHERE ' + @COLUMNNAME
+ ' LIKE ''''' + @SEARCHSTRING + ''''' '') ;'
PRINT @sql
EXEC (@SQL)
FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS
GO
Lowell
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply