August 30, 2007 at 7:37 am
i'm trying to troubleshoot an SSIS package that is supposedly failing to import some records. i've been given details of one of the records it's supposedly failed to import. so, i've opened the db backup file on the day which it was to be imported and search for the details using MS Notepad and can see the record is in there. however, when i restore the db backup file and search for the same record details, it's not found in the table/column where i expect it. so, i know the missing imported record is somewhere in my db, but in which table and which column?
i'd like to loop through all the tables and all columns to search for this record. is this possible with T-SQL?
tks
August 30, 2007 at 8:35 am
Hi Steven,
Not sure if this is teh most process affective way of doing it but it's a good starting block I think:
CREATE TABLE ##VtblTables
(
ROWID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
,[TableName] VARCHAR(1000)
,[ColName] VARCHAR(1000)
)
DECLARE @VinLoop INT
DECLARE @VinLoopMAX INT
DECLARE @VvcSQL VARCHAR(4000)
DECLARE @VvcValue VARCHAR(2000)
DECLARE @VvcColName VARCHAR(1000)
DECLARE @VvctblName VARCHAR(1000)
--***************************************
--TYPE IN THE VALUE YOU LOOKING FOR BELOW
--***************************************
SELECT @VvcValue = 'Chris'
--INSERT ALL TABLE NAMES AND COLUMS HERE
INSERT INTO ##VtblTables
(
[TableName]
,[ColName]
)
SELECT
[T].[Name] as [TableName]
,[C].[Name] as [ColName]
FROM sys.tables [T]
INNER JOIN sys.Columns [C]
ON [C].[Object_ID] = [T].[Object_ID]
SELECT
@VinLoop = 1
,@VinLoopMAX = MAX(ROWID)
FROM ##VtblTables
WHILE (@VinLoop <= @VinLoopMAX)
BEGIN
SELECT
@VvcColName = [ColName]
,@VvctblName = [TableName]
FROM ##VtblTables
WHERE ROWID = @VinLoop
SELECT @VvcSQL =
'IF EXISTS(SELECT ' + @VvcColName + ' FROM ' + @VvctblName + ' WHERE CAST(' + @VvcColName + ' as VARCHAR)= ''' + @VvcValue + ''')
BEGIN
PRINT ''FOUND IN TABLE:' + @VvctblName + ' IN COLUMN:' + @VvcColName + '''
END'
EXEC (@VvcSQL)
SET @VinLoop = @VinLoop + 1
END
DROP TABLE ##VtblTables
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 30, 2007 at 11:27 am
thanks christopher. worked like a charm.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy