Tags

, , , , , ,

I have done a fair bit of programming, but not a lot of SQL. This new code, discovered today with the help of my colleague, does two very useful things:

  1. How to list all tables and columns in a database (emphasised),
  2. Looping through a results set for processing.

I’ve seen a few examples of this type of code, and there has nearly always been something wrong. The great thing here is that, assuming you’re using SQL Server 2008, it will work with any and every database you have.

I am told that to “DEALLOCATE” your cursor is very important.

DECLARE @aTable nVarChar(100);
DECLARE @aColumn nVarChar(100);
DECLARE ResultList CURSOR FOR
  SELECT o.name,c.name FROM sys.columns c
    INNER JOIN sys.objects o ON c.object_id=o.object_id  
  ORDER BY o.name,c.column_id
OPEN ResultList
  FETCH NEXT FROM ResultList INTO @aTable, @aColumn
  WHILE (@@fetch_status <> -1)      
  BEGIN
    PRINT 'Table & Column :: '+@aTable+' -> '+@aColumn
    FETCH NEXT FROM ResultList INTO @aTable, @aColumn
  END
CLOSE ResultList
DEALLOCATE ResultList

I hope this helps somebody!

Advertisements