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:
- How to list all tables and columns in a database (emphasised),
- 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!