So for this installment of SQL tips and tricks, I have one of my favorites. It seems like I can never find a column in a table when I need it. I know it's there, and I remember that maybe it starts with an "h" or a "c", but that's as far as I can get. It gets worse when somebody misspelled the column name in the first place.
Well, I came up with a solution for this "problem" of mine.
DECLARE @columnName VARCHAR(100), @sql VARCHAR(8000), @tableName VARCHAR(100) SET @sql = 'SELECT ' SET @tableName = 'SampleTable' DECLARE columnCursor CURSOR FOR SELECT sc.[name] FROM sys.tables st WITH (NOLOCK) INNER JOIN sys.columns sc WITH (NOLOCK) ON st.object_id = sc.object_id WHERE st.[name] = @tableName ORDER BY sc.[name] OPEN columnCursor FETCH NEXT FROM columnCursor INTO @columnName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = @sql + @columnName + ', ' FETCH NEXT FROM columnCursor INTO @columnName END CLOSE columnCursor DEALLOCATE columnCursor SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1) SET @sql = @sql + ' FROM ' + @tableName SELECT @sql
No comments:
Post a Comment