A handy SPROC for all DBA and developers

I felt it bit boring today. so, thought of to go for a permanent solution on this, where I will give my table name and in result I will get column names in comma separated format.

Objective

Our main motto is to save the key strokes as much as possible while writing codes so that, we will get more time for analysis and an error free code. No doubt, Microsoft integrated a nice intellisense in our all IDE, which makes us a SMART developer. Let's do something more...Let's put some flavor in it.

Today we are going to write a proc, where input variable is table name and column names are in the result set.

Using the code

Here we go!

Below code creates a stored procedure with an user input variable '@TblName', which takes the variable(Table Name) and gives us all column names of that table with comma delimited result. 

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ColNames]') AND type in (N'P', N'PC'))
begin
DROP PROCEDURE [dbo].[ColNames]
end
go
CREATE PROC ColNames (@TblName varchar(50))
as
begin
SELECT
ColumnNames = substring((SELECT ( ', ' + Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name=@TblName
FOR XML PATH( '' )
), 3, 1000 )
end

I know, this is a very simple SP and no rocket science codes are written behind but this SP will differentiate from other developers. Think once, suppose we have to join two(more) tables and have 20(more) columns in select then how we are gonna do that? either drag and drop the columns from object explorer or write all the column names. ahh! all developers do that.. tell something different.. execute this SP and copy all the column names from result set to SELECT statement.. Now how does this sound like?? bit smarter right?? :)

Anyways, I got a smarter way today to achieve my things in daily development.

Conclusion