In an aim to save time and key strokes while writing codes.
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.
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'))
DROP PROCEDURE [dbo].[ColNames]
CREATE PROC ColNames (@TblName varchar(50))
ColumnNames = substring((SELECT ( ', ' + Column_Name)
FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name=@TblName
FOR XML PATH( '' )
), 3, 1000 )
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.
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!