October 22, 2008 at 12:54 pm
One of our developers accidentally deleted a record from some of our tables.We want to just restore the deleted record .But the problem is some of the tables are showing up that while others are not and we have so many tables that we can't check each and every if that record is there or lost.I have an idea regarding that is to create a cursor and check every table in that database and see if the record is present then don't update it otherwise insert that record from the restored database
October 22, 2008 at 12:56 pm
can someone help me in writing a query for the described task
October 22, 2008 at 11:09 pm
Each table have their own (usually) unique structure. INSERT command might be created specifically for each table. If you have other tables on your db, you might inadvertently INSERT a record to a table that don't need it. Do you have a list of tables to check or at least a patter of table names?
-- CK
October 22, 2008 at 11:30 pm
batthula.swetha (10/22/2008)
can someone help me in writing a query for the described task
give me some time please
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 22, 2008 at 11:35 pm
batthula.swetha (10/22/2008)
can someone help me in writing a query for the described task
Well i use this query. this returns the tablename.columnaname and the value in which the value it finds:
i would advise you to run this on the backup database as this will consume more resources based on the size of the database::
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
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'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
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)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(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
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 23, 2008 at 8:23 am
Thankyou all.We were able to restore the record .We found that record was missing in one of our main tables.When we restored record into that table ,all the child tables got populated.Someone has deleted that record from that table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply