Thursday, March 20, 2014

SQL Tips and Tricks - Part 2

Today has been a flurry of posts.  I've had a ton on my mind and no time to put it up here so I'm kinda making up for lost time.  Going forward I think I'm going to try to stick to small posts, but do them very often.

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