Monday, March 14, 2011

Find Tables in SQL by column names

if you are like me and often get called in to stabilize a disaster, you will need to get an understanding of the database really fast, and seen as its a disaster there will be no-one to explain the schema

This useful script will help you to find the links



find tables in sql with cols =


DECLARE search_Cursor CURSOR
FOR select o.name from sysobjects o where o.name like 'XXX%' and type = 'u' order by o.name

OPEN search_Cursor

declare @TableName varchar(100)

FETCH NEXT FROM search_Cursor
INTO @TableName


WHILE @@FETCH_STATUS = 0
begin

declare @ExecString varchar(250)

select @ExecString = ' select * from ' + @TableName + ' where Status = ''' + 'd' + ''''

print @ExecString
exec (@ExecString)


FETCH NEXT FROM search_Cursor
INTO @TableName

end

CLOSE search_Cursor
DEALLOCATE search_Cursor
GO

No comments: