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

Rate

Share

Share

Rate