Dynamically Unpivot Given Table.

UNPIVOT performs by rotating columns into rows.Using below script you can unpivot any given table
by mentioning key columns.

IF OBJECT_ID('[dbo].[unpivot_dynamically]','P') IS NOT NULL

DROP PROCEDURE [dbo].[unpivot_dynamically]

GO

CREATE procedure [dbo].[unpivot_dynamically]

@schemaname varchar(512)

,@srcTblName varchar(512)

,@keycol varchar(512)

,@pivotCol varchar(512)=null

,@valueCol varchar(512)=null

,@debug bit=0

as

begin

set nocount on

if ( @schemaname is null or @srcTblName is null )

---- it will check the any input parameter is NULL

begin

print'input parameters are null, please provide proper values'

return(1)

end

DECLARE @CASTCOLUMNS varchar(1024),@COLUMNLIST VARCHAR(1024),@keycoumns varchar(1024),@SQLSTRING NVARCHAR(1024),@PARAMDEFINATION NVARCHAR(1024)

SELECT @keycoumns=''''+REPLACE(@keycol,',',''',''')+''''

SELECT @SQLSTRING='SELECT @COLUMNLIST=coalesce(@COLUMNLIST,'''')+''[''+COLUMN_NAME+'']''+'','' from information_schema.columns

where table_name ='''+@srcTblName+'''

and TABLE_SCHEMA='''+@schemaname+'''

and COLUMN_NAME not in ('+@keycoumns+')

'

SELECT @PARAMDEFINATION='@srcTblName nvarchar(512),@schemaname nvarchar(512),@keycoumns nvarchar(512),@COLUMNLIST NVARCHAR(1024) OUTPUT'

EXECUTE sp_executesql @SQLString,@PARAMDEFINATION,

@srcTblName = @srcTblName

,@schemaname=@schemaname

,@keycoumns=@keycoumns

,@COLUMNLIST =@COLUMNLIST OUTPUT;

SELECT @COLUMNLIST=SUBSTRING(@COLUMNLIST,1,LEN(@COLUMNLIST)-1)

SELECT @SQLSTRING='select @CASTCOLUMNS=coalesce(@CASTCOLUMNS,'''')+''coalesce (cast (['' + COLUMN_NAME + ''] as varchar (max)), ''''9876543212345678'''') as ''+ ''[''+COLUMN_NAME+'']''+'','' from information_schema.columns

where table_name ='''+@srcTblName+'''

and TABLE_SCHEMA='''+@schemaname+'''

and COLUMN_NAME not in ('+@keycoumns+')

'

SELECT @PARAMDEFINATION='@srcTblName nvarchar(512),@schemaname nvarchar(512),@keycoumns nvarchar(512),@CASTCOLUMNS NVARCHAR(1024) OUTPUT'

EXECUTE sp_executesql @SQLString,@PARAMDEFINATION,

@srcTblName = @srcTblName

,@schemaname=@schemaname

,@keycoumns=@keycoumns

,@CASTCOLUMNS =@CASTCOLUMNS OUTPUT;

SELECT @CASTCOLUMNS=SUBSTRING(@CASTCOLUMNS,1,LEN(@CASTCOLUMNS)-1)

select @SQLString = 'SELECT ' + @keycol + ',' + @pivotCol + ', nullif(' + @valueCol + ',''9876543212345678'' ) as '+@valueCol+'

FROM (SELECT ' + @keycol + ', ' +@CASTCOLUMNS +'

FROM '+@schemaname+'.'+@srcTblName+ ' ) p

UNPIVOT

(' + @valueCol+ ' FOR ' + @pivotCol + ' IN

( '+ @COLUMNLIST + ' )

)AS unpvt'

IF @debug=1

print @SQLString

EXEC(@SQLString)

end

GO
EX:::

Create table Unpivot_test

(

id int,

name varchar(100),

age int,

city varchar(100),

dob datetime,

salary money,

comment text

)

go

insert into Unpivot_test

select 1,'ABC',12,'waltham','12/2/2000',4000,'testing1'

union all

select 2,'DEF',13,'natic','12/5/2000',3000,'testing2'

union all

select 3,'GHY',12,'boston',null,null,null

SELECT * FROM Unpivot_test

Using Above Sp, unpivoting above table

EXEC [dbo].[unpivot_dynamically]

@schemaName = 'dbo',@srcTblName = 'Unpivot_test',

@keyCol = 'id,name',

@pivotCol = 'property_name',

@valueCol = 'Property_value',

@debug=0

Identify your different instances query windows with different color codes.

If you work/connect to multiple instances at same time, Its very difficult identify which query window connected which server.Some times it will confuse.(I know in the below it will display server name)
Color coding the status bar in the Query window in SQL Server Management Studio is only available if you are using SQL Server 2008 Management Studio. Although I must note that I've found that using versions prior to 2008 R2 SP1

Ex:
If you are connected to DEV,UAT,PRD instances, and opened 3 query windows.(one query window from each instance), So without moving your mouse cursor to top you cannot tell which window connected to which server.
Note: This color codes only possible with registered servers.I assume you already registered required instances.
I registered 3 instances to my management studio.

In The above image you can see all the three windows has same color.

To color code a connection, you first need to open up the Registered Server view. Under the "Local Server Groups" item of the "Registered Server" view you will be defining your servers and how they will be color coded. Figure 2 shows three of my local instances that I want to color code. Right click and select properties of the registered server.

Now select connection properties window, and select Use custom color and select required color for your instance.

Here I selected Red for DEV, Green for my UAT and Blue for PRD.

Now open one new query window for each instance, see each window has one color.
Here I selected Horizontal tab group view to see all the colors.

How to share Temp table data between stored procs

a local temporary table created in stored proc sp1 is visible only in that same stored proc as well
as in any stored proc called by sp1 directly or indirectly, that is it is a child in the calling
hierarchy of sp1.
The temporary table will not be visible in stored procs that are not called by sp1 directly or indirectly,
that is they are outside of the calling hierarchy of sp1.
Examples

create PROCEDURE [dbo].[test01]

as begin

insert #temp_test01(id) values(1)

insert #temp_test01(id) values(2)

insert #temp_test01(id) values(3)

select * from #temp_test01

end

create PROCEDURE [dbo].[test02]

as begin

select * from #temp_test01

end

begin

create table #temp_test01(id int)

exec dbo.test01 -- if the table is created in here, then dbo.test02 should be called from inside

-- test01, otherwise it won't see the table

exec dbo.test02

end

Same goes for dynamic SQLs. The execution context of exec(SQL) is different from the execution context
of the host stored procedure, thus a temp table crate inside the dynamic SQL cannot be seen in the
stored proc itself that executes the dynamic sql.
In case the columns of the temp table are not known upfront, one can create the temp table with static sql
then add the columns to it with dynamic sql.

begin --

drop table #temp_test01

create table #temp_test01(id int)

end

declare @sql varchar(2000)

select @sql = 'alter table #temp_test01 add id2 int '

exec (@sql)

select @sql = 'insert into #temp_test01 values (100, 200) '

exec (@sql)

exec dbo.test01

exec dbo.test02

end

Find current identity value for all tables in given database.

USE TEST

GO

IF OBJECT_ID('tempdb..#identityinfo','U') is not null

drop table tempdb..#identityinfo

GO

create table #identityinfo

(

id int identity(1,1) not null,

table_name varchar(256),

Column_name varchar(256),

Data_type varchar(256),

max_value bigint,

current_value bigint

)

GO

declare @table_name varchar(256),@column_name varchar(256),@data_type varchar(256),@Current_idenity bigint,@max_value bigint

declare C_identity cursor for select s.name+'.'+o.name,c.name,sc.DATA_TYPE from sys.columns c join sys.objects o on c.object_id = o.object_id

join sys.schemas s on s.schema_id = o.schema_id

join INFORMATION_SCHEMA.COLUMNS sc on sc.Table_schema=s.name and sc.TABLE_NAME=o.name and sc.COLUMN_NAME=c.name

where o.type = 'U' and c.is_identity = 1

order by o.name

OPEN C_identity

fetch next from C_identity into @table_name,@column_name,@data_type

while @@FETCH_STATUS=0

begin

SET @max_value = CASE @data_type

WHEN N'TinyInt' THEN 255

WHEN N'SmallInt' THEN 32767

WHEN N'Int' THEN 2147483647

WHEN N'BigInt' THEN 9223372036854775807

END;

select @Current_idenity=ident_current(@table_name)

insert into #identityinfo(table_name ,Column_name ,Data_type ,max_value ,current_value)

select @table_name,@column_name,@data_type,@max_value,@Current_idenity

fetch next from C_identity into @table_name,@column_name,@data_type

end

select * from #identityinfo

close C_identity

deallocate C_identity

Excel file loading problems. Here is the solution.

While loading/reading Excel file from SQL server using import/export wizard,it will try to guess the data type of the columns to be imported.Once it determines the data type of a column it will ignore the data that is not conforming to the data type and does not do any implicit conversion. The result is that if your column has number and text then most likely your number or your text will became NULL after they are imported into SQL server.( It will first 8 rows and determine type of the the column).

There are two ways we can address this problem.
1)open your excel file, copy your column to notepad and then format the column as text in excel. After that copy the data from notepad and paste it back to the column as text. After that the column can be import with out losing any values.

2) link the Excel file into SQL server by running the following statement.
Here the trick is IMEX=1( I will scan entire file)

select * into sample4

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\SALamppostSamples4_DOS.xls;HDR=YES;IMEX=1',

'SELECT * FROM [Sheet1$]')

3) change below value
start-->run-->regedit-->
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Excel
change “TypeGuessRows” to 0

I prefer second method, because we can schedule jobs with this method.

If we use bulk insert, upfront we need to create table with required columns. Here is the solution, it will create table with with required columns and load the data into table

CREATE PROCEDURE dbo.dynamic_table_loading

(

@file_path varchar(1024),

@table_name varchar(256),

@file_type varchar(100)='tab',

@column_length varchar(100)='1024',

@debug bit=0

)

AS

/****************************************************

USE:- This stored procedure is used to load datafrom given file path to

given table name.This stored procedure will automatically create required table with columns

(If we use bulk insert, we need to know number of columns in flat file, but using this stored procedure

we it will create table with with required columns and load the data into table)

PARAMETERS:

@file_path:-full path of the file

@table_name:-table name(it will drop this table, if exists with this name)

@file_type:-type of the file(default tab deliminated) (or)',' (or)'|'

@column_length=lengh of the all the columns in table(default is 1024)

@debug=default is 0 :0 it will not print any messages)

:1 it will print all the messages generated by storedproc(use while debugging storedproc)

EXEC dbo.dynamic_table_loading

@file_path='C:\test.xls' ,

@table_name='testing' ,

@file_type=',',

@column_length='4000',

@debug=1

*******************************************************/

BEGIN

BEGIN TRY

SET NOCOUNT ON

declare @procName varchar(100), @dbName varchar(256), @date varchar(20), @ntab varchar(200),

@nln varchar(4), @rows varchar(10)

declare @ErrMsg nvarchar(4000), @ErrSev varchar(3), @ErrSt varchar(4), @ErrProc nvarchar(126), @ErrLn nvarchar(10),

@ErrNr nvarchar(10), @ErrLoc nvarchar(300)

select @procName=DB_NAME()+'.'+ss.name+'.'+so.name from sys.objects so, sys.schemas ss

where so.object_id = @@PROCID and so.schema_id=ss.schema_id

select @date = cast(getDate() as varchar(20)),@nln=''+char(10)+' ', @dbName=DB_NAME(),

@ntab=@procName+'> '

DECLARE @bulkCmd varchar(4000),@SQL VARCHAR(1024),@tableSql varchar(1024),

@replace varchar(100),@last_column varchar(100)

if @debug=1

raiserror('Entering procedure "%s"with filepath%s,table_name:%s and file_type',0,1,@file_path,@table_name,@file_type)with nowait

---if file_type tab deliminated , it will replace tab with char(9)

if @file_type='tab'

set @file_type=CHAR(9)

SET @replace='] varchar('+@column_length+'), ['

IF OBJECT_ID(N'tempdb..#temp') IS NOT NULL

drop table #temp

create table #temp (line varchar(max))

SET @SQL='IF OBJECT_ID('''+@table_name+''') IS NOT NULL

drop table '+@table_name+''

if @debug=1

print @SQL

EXEC(@SQL)

SET @bulkCmd='bulk insert #temp from '''+@file_path +''' with (FIRSTROW=1,LASTROW=1)'

if @debug=1

print @bulkCmd

EXEC (@bulkCmd)

--build the temporary table dynamically, yet create it with static sql.

SET @SQL='create table '+@table_name+'( placeHolder int)'

if @debug=1

print @SQL

EXEC(@SQL)

select top 1 @tableSql='alter table '+@table_name+' add ['+LIne from #temp

if @debug=1

Print @tableSql

set @tableSql = Replace(@tableSql, @file_type, @replace)

if @debug=1

Print @tableSql

set @tableSql = @tableSql + '] varchar('+@column_length+')'

if @debug=1

Print @tableSql

EXEC (@tableSql)

SET @SQL='alter table '+@table_name+' drop column placeHolder'

if @debug=1

print @SQL

EXEC(@SQL)

SET @bulkCmd='bulk insert '+@table_name+' from '''+@file_path +'''

with( FIRSTROW=2,

fieldterminator ='''+@file_type+''',

rowterminator = '''+char(10)+''' )'

if @debug=1

print @bulkCmd

EXEC (@bulkCmd)

if @debug=1

raiserror('input file %s successfully loaded into table %s',0,1,@file_path, @table_name)

select @last_column=QUOTENAME(name) from sys.columns where object_id =

object_id(@table_name) and column_id=(select max(column_id)

from sys.columns where object_id =object_id(@table_name))

if @debug=1

raiserror('updating last column %s in table:%s,if it has any special characters ',0,1,@last_column, @table_name)

SET @SQL='UPDATE '+@table_name+' set '+@last_column+'=ltrim(rtrim(rEPLACE(REPLACE(REPLACE('+@last_column+', CHAR(10), ''''), CHAR(13), ''''), CHAR(9), '''')))'

if @debug=1

print @SQL

EXEC(@SQL)

if @debug=1

raiserror(' Leaving procedure %s at %s', 0, 1, @procName, @date) with nowait

END TRY

BEGIN CATCH

select @ErrMsg =ERROR_MESSAGE(), @ErrSev =ERROR_SEVERITY(), @ErrSt =ERROR_STATE(),

@ErrProc =ERROR_PROCEDURE(), @ErrLn = ERROR_LINE(), @ErrNr = ERROR_NUMBER()

raiserror('Failed to execute %s,procname %s Error nr %s, state %s, sev %s; line %s, %s with msg: %s',

16,-1,@procName, @nln,@ErrNr,@ErrSt,@ErrSev,@ErrLn,@nln,@ErrMsg)

END CATCH

END

GO