Google

Wednesday, August 15, 2007

DisplayColumnNames

This sp will return the column names for a table(s) and form a "Select Statement" for a table(s). We can pass a table name as a parameter to display a "Select Statement" for that table.

if exists (select * from sysobjects where id = object_id(N'[dbo].[spDisplayColumnName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spDisplayColumnName]
GO
Create Proc spDisplayColumnName (@TableName Varchar(100)='') As
--Exec spDisplayColumnName 'Account'
--Or
--Exec spDisplayColumnName
Declare @ColumnName Varchar(8000), @TableID Int

Set Nocount On

If Len(@TableName) = 0
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where xType = 'U' Order By SO.Name
End
Else
Begin
If Not Exists(Select 1 From SysObjects Where ID = Object_ID(@TableName) And xType = 'U')
Begin
Print 'Passed parameter [' + @TableName + '] is not an User table'
Return 0
End
Else
Begin
DECLARE DisplayColumnName_Cursor CURSOR FOR
Select SO.ID From SysObjects SO Where ID = Object_ID(@TableName)
And xType = 'U' Order By SO.Name
End
End

OPEN DisplayColumnName_Cursor

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID

WHILE @@FETCH_STATUS = 0
Begin
Set @ColumnName = ''
Select @ColumnName = @ColumnName + ', ' +Name
From SysColumns Where ID = @TableID
Order By Name
Print Object_Name(@TableID)
Select 'Select ' + Right(@ColumnName,Len(@ColumnName)-1) + ' From ' + Object_Name(@TableID)

FETCH NEXT FROM DisplayColumnName_Cursor
INTO @TableID
End
CLOSE DisplayColumnName_Cursor
DEALLOCATE DisplayColumnName_Cursor

Set Nocount Off
Return 0

No comments: