A handy SPROC for all DBA and developers

Deviprasads
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 2409 red flag
Rating: 5 out of 5  
 1 vote(s)

In an aim to save time and key strokes while writing codes.

Introduction

The whole story begins from a single sentence 'Need leads to an innovation'..

In our daily job, how many times we write 'select top 1 * ' or use object explore to know/get the column names of a table into a query window? Don't you guys think it's a bit tedious job doing select top 1 * all the time to know/get only the column names?

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
Execute on your DEV environment and select the DB where you need to publish it.


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


We saved key strokes today!.. in other way we saved our time no matter whether it's a min or sec or m-sec. Only A software engineer knows how a sec matters for him/her? He/She breaks his/her head in multiple ways to reduce a sec time in an execution..

Remember.. 'Needs leads to an innovation'. I was in need so got an idea to adopt something new. So, friends don't be satisfied with anything otherwise you will be outdated. Keep rolling things...and take a min to share them up here!

If you use any smart techniques in your DEV, then please please share those.. Knowledge gains only on sharing!


Page copy protected against web site content infringement by Copyscape

About the Author

Deviprasads
Full Name: Deviprasad sahoo
Member Level: Starter
Member Status: Member
Member Since: 6/8/2012 3:40:15 AM
Country: India
S Devi Prasad
http://www.sqlindia.com
currently working as ASE in an USA based health care company.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)