Similar sort of Cursor, but slightly different

Got fed up of modifying the previous cursor to do this simpler event.

Basically finds every table in a database and then returns the first row, prefixing the information returned for each table with the tablename.

Saves time running a select statement for each table and useful for finding a table that you know must exist, but isnt clearly named on the database.


declare @command varchar(255)
declare @tablename sysname
declare @count int
DECLARE StatusCursor CURSOR FOR

select a.name from sysobjects a where a.type ='U' order by a.name

Open StatusCursor
Fetch next from StatusCursor into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
exec ('select top 1 ' + '''' + @tablename + ''', * from ' + @tablename + '')
Fetch next from StatusCursor into @tablename
END
CLOSE StatusCursor
Deallocate StatusCursor


Cursor code for table objects

I had done something cross databases before using sysdatabases, but this time I needed to search for specific text in any column on an table in a known database.


declare @command varchar(255)
declare @tablename sysname
declare @columnname sysname
declare @count int
DECLARE StatusCursor CURSOR FOR

select a.name,b.name from sysobjects a join syscolumns b on a.id = b.id where a.type ='U' order by a.name

Open StatusCursor
Fetch next from StatusCursor into @tablename, @columnname
WHILE @@FETCH_STATUS = 0
BEGIN
if(@columnname = 'FOO1')
begin
print @tablename
end
Fetch next from StatusCursor into @tablename, @columnname
END
CLOSE StatusCursor
Deallocate StatusCursor