Dynamically Pivot OR Unpivot given table

/****** Object:  StoredProcedure [dbo].[PVIOT_OR_UNPIVOT _GIVENTABLE]    Script Date: 04/10/2014 14:36:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Alter Procedure [dbo].[Pivot_Unpivot_Table]

(

       @dbname varchar(512)

      ,@schemaname varchar(512)

      ,@srcTblName varchar(512)

      ,@trgtTblName varchar(512)

      ,@keyCols varchar(8000)

      ,@pivotCol varchar(512)

      ,@valueCol varchar(512)

      ,@debug bit=1

      ,@pivotorunpivot varchar(100)

)

/* Test:

---Pivot test

IF object_ID('test', 'U') IS NOT NULL

      drop table test

create table test ( studebt_id int,student_name varchar(100), attr_name varchar(100), attr_value varchar(100))

insert into test

select 1,'bhasker', 'age', '24'

union all select 1,'bhasker', 'sex', 'F'

union all select 2,'suresh', 'age', '28'

union all select 2, 'suresh','sex', 'M'

union all select 3,'raju', 'age', '28'

union all select 3, 'raju','sex', null

union all select 4, 'prabhu','sex', 'F'

SELECT * from test

EXEC [dbo].[Pivot_Unpivot_Table]

       @dbname ='test_database'

      ,@schemaname='dbo'

      ,@srcTblName ='test'

      ,@trgtTblName='test_pivot'

      ,@keyCols ='''studebt_id'',''student_name'''

      ,@pivotCol='attr_name'

      ,@valueCol='attr_value'

      ,@debug =0

      ,@pivotorunpivot='PIVOT'

select * from test_pivot

--UNPIVOT test------

IF object_ID('test', 'U') IS NOT NULL

      drop table test

create table test (student_id INT, student_Name varchar(100), age int, sex varchar(100),height float ,salary money, DOB datetime)

GO

insert into test

select 1,'bhasker', 24, 'M', 172.2, 1456.99, '1984-06-24'

union all select 2,'suresh', 29, 'M', 180,NULL, NULL

union all select 3, 'raju', 34, NULL, 176.9, 7899.9, null

union all select 4, 'prabhu', null, 'M', 189, NULL, '1987-09-17'

select * from test

EXEC [dbo].[Pivot_Unpivot_Table]

       @dbname ='Test_database'

      ,@schemaname='dbo'

      ,@srcTblName ='test'

      ,@trgtTblName='test_unpivot'

      ,@keyCols = N'''student_id'', ''student_Name'''

      ,@pivotCol='attributeName'

      ,@valueCol='attributevalue'

      ,@debug =0

      ,@pivotorunpivot='UNPIVOT'

select * from test_unpivot

drop table test_unpivot

           

*/

AS

BEGIN

      SET NOCOUNT ON

      declare @database_name varchar(100),@server_name varchar(256), @start_Time smalldatetime,

            @startLoadTime smalldatetime, @proc varchar(256), @date varchar(30), @loadSPid varchar(4),

            @netAddr nchar(12),@clPrgName nvarchar(60), @spLoginTime varchar(30), @hostName nvarchar(20),

            @spLoginName nvarchar(50),@nln varchar(6), @ntab varchar(200),@message varchar(1024),

            @procName varchar (100),@SQL nvarchar (1024), @studentsTlb varchar(1024),@StudentsunpivotedTlb varchar (1024),

            @colname varchar (100),@conv_cols nvarchar (4000),@cols NVARCHAR(4000)

           

           

       declare @ErrMsg nvarchar(4000), @ErrSev int, @ErrSt int, @ErrProc nvarchar(126),

                        @ErrLn int, @ErrNr int, @ErrLoc nvarchar(300),@P_session_name varchar(256),

                        @Rowstxt int,@inclTxtColsNoCast varchar(512)

                       

       --DECLARE @column_List nvarchar(4000), @Paramdefination nvarchar(1024)                 

                       

                       

                       

       select @proc=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     @database_name=DB_NAME(),@server_name = @@ServerName , @start_Time = getDate(),

                  @startLoadTime = getDate(), @date = convert(varchar(30),getDate(),109),

                  @nln = ''+char(10)+'    ', @ntab=@proc+'> '

    

       select @loadSPid=sp.spid, @hostName=substring(sp.hostname,0,len(sp.hostname)),

                  @spLoginName =substring(sp.loginame,0,len(sp.loginame)),

                  @spLoginTime=convert(varchar(20),sp.login_time,109),@netAddr=sp.net_address,

                  @clPrgName = program_name

       from sys.sysprocesses sp where sp.spid= @@spid

     

       

       BEGIN TRY

                                         

          IF @debug=1

                  raiserror('Entering Procedure %s at %s%s', 0,1,@proc,@date,@nln) WITH NOWAIT     

                 

            IF (@dbname IS NULL OR @schemaname IS NULL OR @srcTblName IS NULL OR @trgtTblName IS NULL

                                          OR @keyCols IS nULL OR @pivotCol IS NULL OR @valueCol IS NULL )

            BEGIN

                  raiserror('Input parameters cannot be null, Please check the input parameter values for below variables

                                    (@dbname, @schemaname, @srcTblName, @trgtTblName, @keyCols, @pivotCol, @valueCol ',16,-1) WITH NOWAIT

            END

                 

            SELECT @SQL='IF OBJECT_ID('''+@dbname+'.'+@schemaname+'.'+@trgtTblName+''', ''U'') is not null

                  drop table '+@dbname+'.'+@schemaname+'.'+@trgtTblName +''

            IF @debug=1

            BEGIN

                  raiserror('Drop target table if exists using below query %s%s', 0,1,@nln, @SQL) WITH NOWAIT

            END

            EXEC(@SQL)

           

            IF (@pivotorunpivot='PIVOT') OR (@pivotorunpivot='UNPIVOT')

            BEGIN

                             

                  IF (@pivotorunpivot='PIVOT')

                  BEGIN

                        IF @debug=1

                              raiserror('Generating pivot data for given table %s',0,1, @nln ) with nowait          

                       

                                         

                        DECLARE @column_List nvarchar(4000), @Paramdefination nvarchar(1024)

                        SELECT @column_List=''

                        SELECT @SQL='SELECT @column_List=@column_List+ ''[''+'+@pivotCol+'+''],'' FROM

                         (SELECT DISTINCT '+@pivotCol+' from  '+ @dbname+'.'+@schemaname+'.'+@srcTblName +') a'

                       

                       

                        SELECT @Paramdefination='@column_List varchar(1024) OUTPUT'

                        EXEC sp_executesql @SQL, @paramdefination , @column_List=@column_List output

                        SELECT @column_List=LEFT(@column_List, LEN(@column_List)-1)

                                               

                        SELECT @SQL='SELECT '+ replace(@keycols,'''','') + ', '+ @column_List+ '

                                                 INTO '+ @dbname+'.'+@schemaname+'.'+@trgtTblName +'

                                                FROM

                                                (SELECT '+ replace(@keycols,'''','') + ' , '+ @pivotCol+ ' , '+ @valueCol +'

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

                                                PIVOT

                                                (

                                                MAX ('+@valueCol+')

                                                FOR '+@pivotCol+' IN

                                                ( '+@column_List+' )

                                                ) AS pvt

                                                ORDER BY pvt.'+ replace(@keycols,'''','') + ''

                        IF @debug=1

                              raiserror('Pivoting using below query %s %s',0,1, @nln, @SQL)WITH NOWAIT

                       

                        EXEC(@SQL)

                                                                 

                       

                  END  

                 

                  IF (@pivotorunpivot='UNPIVOT')

                  BEGIN

                        IF @debug=1

                              raiserror('Generating unpivot data for given table %s',0,1, @nln ) with nowait           

           

           

                        SELECT @sql ='SELECT  @conv_cols =  Coalesce(@conv_cols,'''')+'', ''

                               + ''ISNULL(convert (varchar(1024),''+QUOTENAME(name)+''),'''''''') as ''+QUOTENAME(name)

                               FROM sys.columns

                               WHERE [object_id] = OBJECT_ID('''+@srcTblName+''')

                               AND name NOT IN  ('+@keycols+')';

                        SELECT @Paramdefination='@conv_cols nvarchar(4000) OUTPUT'

                        EXEC sp_executesql @sql, @Paramdefination, @conv_cols=@conv_cols OUTPUT

                       

                                               

                        SELECT @sql ='SELECT  @cols =   Coalesce(@cols,'''') +'', '' + QUOTENAME(name)

                        FROM sys.columns

                        WHERE [object_id] = OBJECT_ID('''+@srcTblName+''')

                        AND name NOT IN  ('+@keycols+')';

                        SELECT @Paramdefination='@cols nvarchar(4000) OUTPUT'

                        EXEC sp_executesql @sql, @Paramdefination, @cols=@cols OUTPUT

                       

                       

                        SELECT @sql = N'SELECT ' + replace(@keycols,'''','') + ', '+@pivotcol+ ', NULLIF('+@valuecol+','''') as '+ @valuecol+'

                                           INTO '+ @dbname+'.'+@schemaname+'.'+@trgtTblName +'

                                          FROM

                                           (

                                          SELECT ' + replace(@keycols,'''','') + @conv_cols + '

                                          FROM  '+ @dbname+'.'+@schemaname+'.'+@srcTblName +'

                                          ) AS cp

                                          UNPIVOT

                                          (

                                          '+@valuecol+' FOR '+@pivotcol+' IN (' + Stuff(@cols, 1, 1, '') + ')

                                          ) AS up;';

       

                        IF @debug=1

                              raiserror('UnPivoting using below query %s %s',0,1, @nln, @SQL)WITH NOWAIT             

           

                        EXEC sp_executesql @sql

                       

                  END

                             

           

            END ELSE BEGIN

                  raiserror ('@pivotorunpivot paarameter values should be pivot or unpivot only(%s)', 16, -1, @pivotorunpivot) WITH NOWAIT

            END

           

           

            SET @date = cast(getdate() as varchar(30))     

            IF @debug=1

                  raiserror('%s Leaving procedure %s at %s %s',0,1,@nln,@procName, @date, @nln ) 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('Original error nr %d, state %d, sev %d; line %d, proc %s %sError msg: %s',

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

                             

      END CATCH

     

END

Solution for Error "The database owner SID recorded in the master database differs from the database owner SID recorded in database 'XXXX'"

Before we are going to solve the problem, we need to know how it happened.
Next time we don't repeat same mistake.
I will explain with following example::
I login to my sql server instance with sqlauthentication login called  "sql_login"
I created database name called "Owner_test", created table with two different Sps
and check the Owner SID in master database and its own database.

USE
GO
/****** Object: Database [Owner_test] Script Date: 01/03/2012 14:30:05 ******
/CREATE DATABASE [Owner_test] ON PRIMARY ( NAME = N'Owner_test', FILENAME = N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\Owner_test.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON ( NAME = N'Owner_test_log', FILENAME = N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\Owner_test_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO-
----create table and insert data
USE Owner_test
Go
Create table Test(id int)
Go
insert into Test select 1 union all select 2 union all select 3
---create two stoted procs
--1)create sp and it will execute with its callerID
create procedure Test_proc1
as begin
select * from Test
END

GO

---2) )create sp and it will Execue with other user
---When you create this type of stored procedure, change database property trustworthy to ON.
ALTER DATABASE [Owner_test] SET TRUSTWORTHY ON GO

select SUSER_SNAME(owner_sid) FROM sys.databases WHERE name='Owner_test'

Here it giving sql_login

--To get the owner SID recorded for the current database owner

SELECT SUSER_SNAME(sid) FROM Owner_test.sys.database_principals WHERE name=N'dbo'

Here it giving sql_login

After successfully designed this database in DEV, now its time to move this database to UAT or PRD.
We can move this database to other server by detach, copy and atach to other server or take backup of database, copy to other server and restore it.
In UAT, I login into server with windows authentication called "'Domain\Name'"
I attched "Owner_test" database into UAT server.


----Now Execute both stored procs in UAT server
EXEC Test_proc1---successfully executed
EXEC Test_proc2---This stored proc given below error.
Msg 916
The , Level 14, State 1, Procedure Test_proc2, Line 0server principal "'Domain\Name'" is not able to access the database "Owner_test" under the current security context.
The problem is Owner ISID in master database and its own database are diferent
--To get ownerSID  from master database
select SUSER_SNAME(owner_sid) FROM sys.databases WHERE name='Owner_test'

Here it giving
'Domain\Name'

 --To get the owner SID recorded for the current database owner
SELECT SUSER_SNAME(sid) FROM Owner_test.sys.database_principals WHERE name=N'dbo'

Here it giving sql_login

Here is the solution fo this problem.
ALTER AUTHORIZATION ON Database::Owner_test TO [Domain\Name]
Now OwnerISID in both places are same and sps will execute sucessfully.


Here is the Automation script, it will check above descrepency in all databases and it will fix OwnserISID problem.

IF OBJECT_ID(N'tempdb..#owners', 'U') IS NOT NULL

DROP TABLE #owners;

CREATE TABLE #owners

(

database_name sysname NOT NULL,

sys_databases_sid varbinary(85) NOT NULL,

sys_databases_owner nvarchar(256) NULL,

sys_users_sid varbinary(85) NULL,

sys_users_owner nvarchar(256) NULL

);

INSERT INTO #owners

(

database_name,

sys_databases_sid,

sys_databases_owner

)

SELECT

name,

owner_sid,

SUSER_SNAME(owner_sid)

FROM sys.databases;

---select * from #owners     

EXEC sp_MSforeachdb 'UPDATE #owners SET sys_users_sid =(SELECT sid

FROM [?].sys.database_principals WHERE name = ''dbo''),      sys_users_owner = (

SELECT SUSER_SNAME(sid)

FROM [?].sys.database_principals

WHERE name = ''dbo'')

WHERE database_name = ''?''

';

SELECT * FROM #owners

WHERE sys_databases_sid <> sys_users_sid OR sys_databases_owner IS NULL;

DECLARE @database_name varchar(100),@owner varchar(100)

DECLARE C CURSOR FOR SELECT database_name, Sys_databases_owner FROM #owners

WHERE  sys_databases_sid <> sys_users_sid     OR sys_databases_owner IS NULL

OPEN C

FETCH NEXT FROM C INTO @database_name,@owner

WHILE @@FETCH_STATUS=0

BEGIN

raiserror('procesing database=%s',0,1,@database_name) with nowait

Print 'ALTER AUTHORIZATION ON DATABASE:: '+@database_name+' to ['+@owner+'] ;'

EXEC ('ALTER AUTHORIZATION ON DATABASE:: '+@database_name+' to ['+@owner+'] ;')

FETCH NEXT FROM C INTO @database_name,@owner

END

CLOSE C

DEALLOCATE C