Dynamic PIVOT Query In SQL server

ALTER PROCEDURE [dbo].[SP_DYNAMIC_PIVOT]
  
    @SELECT_QUERY_COLUMNS VARCHAR(255)='col as col,col',
    @PIVOT_COLUMN VARCHAR(255)='col',
    @VALUE_COLUMN VARCHAR(255)='col',
    @TABLEORSQLWITHDBO VARCHAR(MAX)='dbo.TABLENAME',
    @ORDER_BY_COLUMNS VARCHAR(255) = 'col',
    @Aggregate VARCHAR(20) = 'COUNT',
    @OUTPUT_RESULT_SQL VARCHAR(10) = '',--SQL
    @OUTPUT_RESULT_INTO_TABLENAME VARCHAR(100) = 'RESULT',
    @SQLOutput VARCHAR(MAX) = NULL OUT
AS
BEGIN
  
    DECLARE @PivotValuesCSV NVARCHAR(MAX),
            @PivotSQL NVARCHAR(MAX),
            @DistinctPivotValuesSQL NVARCHAR(MAX),
            @OutputResultsIntoTableSQL NVARCHAR(100);
    --
    SET @DistinctPivotValuesSQL =  'SELECT TOP 1 STUFF((SELECT DISTINCT '', '' + CAST(''[''+CONVERT(VARCHAR,'+ @PIVOT_COLUMN+')+'']''  AS VARCHAR(50)) [text()]
                                FROM '+@TABLEORSQLWITHDBO+' AS a
                                WHERE ISNULL('+@PIVOT_COLUMN+','''') !=  ''''
                                FOR XML PATH(''''), TYPE)
                                    .value(''.'',''NVARCHAR(MAX)''),1,2,'' '') AS PIVOT_VALUES
                                FROM '+@TABLEORSQLWITHDBO+' AS ma
                                ORDER BY ' + @PIVOT_COLUMN + '';
    
    SELECT @OutputResultsIntoTableSQL = CASE WHEN @OUTPUT_RESULT_INTO_TABLENAME IS NULL THEN '' ELSE (' INTO ' + @OUTPUT_RESULT_INTO_TABLENAME + ' ') END;
    CREATE TABLE #Temp(PivotColumnsCSV NVARCHAR(MAX));
    INSERT INTO #Temp EXEC SP_EXECUTESQL  @DistinctPivotValuesSQL;
    SELECT @PivotValuesCSV = PivotColumnsCSV  FROM #Temp;
    DROP TABLE #Temp;    
  
    SET @PivotSQL = 'SELECT * ' + CHAR(13) +
                    @OutputResultsIntoTableSQL + CHAR(13) +
                    'FROM  ( ' + CHAR(13) +
                    '  SELECT ' + @SELECT_QUERY_COLUMNS + ' , ' + @PIVOT_COLUMN + ', ' + @VALUE_COLUMN + ' FROM '+@TABLEORSQLWITHDBO+' AS innr ' + CHAR(13) +
                    '       ) a ' + CHAR(13) +
                    'PIVOT ' + CHAR(13) +
                    '( ' + CHAR(13) +
                    '   '+@Aggregate+'('+@VALUE_COLUMN+') ' + CHAR(13) +
                    '   FOR ' + @PIVOT_COLUMN + ' IN (' + @PivotValuesCSV + ') ' + CHAR(13) +
                    ') piv ' + CHAR(13) +
                    'ORDER BY ' + @ORDER_BY_COLUMNS;
SET @PivotSQL += ' SELECT * FROM ' + @OUTPUT_RESULT_INTO_TABLENAME
SET @PivotSQL += ' DROP TABLE ' + @OUTPUT_RESULT_INTO_TABLENAME
    PRINT @PivotSQL;
    --Return either the SQL or the actual results!
    IF (@OUTPUT_RESULT_SQL = 'SQL')
        --SELECT @PivotSQL AS PivotSQL;
        SELECT @SQLOutput = @PivotSQL;
    ELSE
        EXEC SP_EXECUTESQL @PivotSQL;
END;

Set default value for all columns with specific name within a database SQL Server

ALTER PROCEDURE [dbo].[SP_DefaultValueAllCol]
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'ALTER TABLE ' + t + N' DROP CONSTRAINT ' + c + N';
  ALTER TABLE ' + t + N' ADD CONSTRAINT ' + c + N' DEFAULT (5) FOR [insertOnUTC];'
FROM
(
     SELECT QUOTENAME(s.name) + '.' +   
     QUOTENAME(t.name) AS t,
     QUOTENAME(d.name) AS c
          FROM sys.tables AS t
          INNER JOIN sys.schemas AS s
          ON t.[schema_id] = s.[schema_id]
          INNER JOIN sys.columns AS c
          ON t.[object_id] = c.[object_id]
          INNER JOIN sys.default_constraints AS d
          ON d.parent_object_id = t.[object_id]
          AND d.parent_column_id = c.column_id
WHERE c.name = N'COLUMNNAME'
) AS x;

PRINT @sql;
END

Comma Separated List of all columns in the Database all tables in Sql Server

ALTER FUNCTION [dbo].[ReturnTableCommaSeparted]
(
    @TABLENAME VARCHAR(100) = ''
)
RETURNS @RETURN_LIST TABLE
(
    TABLE_SCHEMA VARCHAR(MAX) NOT NULL,
    TABLE_NAME VARCHAR(MAX) NOT NULL,
    Columns_List VARCHAR(MAX) NOT NULL
)
AS
BEGIN
    INSERT INTO @RETURN_LIST
    (
        TABLE_SCHEMA,
        TABLE_NAME,
        Columns_List
    )
    SELECT TABLE_SCHEMA,
           TABLE_NAME,
           'SELECT ' + STUFF(
                       (
                           SELECT ', ' + C.COLUMN_NAME
                           FROM INFORMATION_SCHEMA.COLUMNS AS C
                           WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
                                 AND C.TABLE_NAME = T.TABLE_NAME
                           ORDER BY C.ORDINAL_POSITION
                           FOR XML PATH('')
                       ),
                       1,
                       2,
                       ''
                            ) 
+ ' FROM ' + T.TABLE_NAME + ' WITH(NOLOCK) ' AS Columns_List
    FROM INFORMATION_SCHEMA.TABLES AS T
    WHERE (
              @TABLENAME = ''
              OR T.TABLE_NAME = @TABLENAME
          );
    RETURN;
END;

Update statistics all databases SQL Server

BEGIN
DECLARE @SQL VARCHAR(1000)  
DECLARE @DB sysname  

DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR  
SELECT [name]  
FROM master..sysdatabases 
WHERE [name] NOT IN ('model', 'tempdb') 
ORDER BY [name] 
     
OPEN curDB  
FETCH NEXT FROM curDB INTO @DB  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
   SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)  
   PRINT @SQL  
   FETCH NEXT FROM curDB INTO @DB  
   END  
    
CLOSE curDB  
DEALLOCATE curDB
END

Missing Index Script for all SQL Server Databases

SELECT dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact * (dm_migs.user_seeks + dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) AS [TableName],
'CREATE INDEX [TAB_INX_' + OBJECT_NAME(dm_mid.object_id, dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN
'_'
ELSE
''
END + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']'
+ ' ON ' + dm_mid.statement + ' (' + ISNULL(dm_mid.equality_columns, '')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN
','
ELSE
''
END + ISNULL(dm_mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_id = DB_ID()
ORDER BY Avg_Estimated_Impact DESC;

Reset identity seed after deleting records in SQL Server

DELETE FROM dbo.TABLENAME WHERE id > 500

DECLARE @MAX INT;  
SELECT @MAX = MAX(ID) FROM TABLENAME AS NCPD;  
dbcc checkident(TABLENAME,reseed,@max)

All Tables Parameters With Isnull Sql Server

BEGIN

DECLARE @TABLENAME VARCHAR(50)='TABLENAME'
DECLARE @COLUMN_LIST VARCHAR(MAX)=''
BEGIN
DECLARE @COLUMN_NAME VARCHAR(MAX)=''
DECLARE @COLUMN_DATA_TYPE VARCHAR(MAX)=''
DECLARE @GENERATED_SCRIPT VARCHAR(MAX)='SELECT ' + char(10)
IF @TABLENAME !=''
BEGIN
    DECLARE CUR CURSOR 
FOR 
SELECT COLUMN_NAME,DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLENAME
ORDER BY ORDINAL_POSITION
OPEN CUR
FETCH NEXT FROM CUR INTO @COLUMN_NAME,@COLUMN_DATA_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@COLUMN_DATA_TYPE ='int')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ',0) AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END
IF (@COLUMN_DATA_TYPE ='varchar')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ','''') AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END
IF (@COLUMN_DATA_TYPE ='tinyint')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ','''') AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END
IF (@COLUMN_DATA_TYPE ='datetime')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ','''') AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END
IF (@COLUMN_DATA_TYPE ='date')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ','''') AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END
IF (@COLUMN_DATA_TYPE ='time')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ','''') AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END
IF (@COLUMN_DATA_TYPE ='decimal')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ',0) AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END
IF (@COLUMN_DATA_TYPE ='bigint')
BEGIN
    SET @GENERATED_SCRIPT = @GENERATED_SCRIPT + 'ISNULL(' + @COLUMN_NAME + ',0) AS ['+ REPLACE(@COLUMN_NAME,'_',' ') + '],' + CHAR(10) 
END

FETCH NEXT FROM CUR INTO @COLUMN_NAME,@COLUMN_DATA_TYPE
END
CLOSE CUR
DEALLOCATE CUR
DECLARE @NEWREMOVESTRING VARCHAR(MAX) =''
SET @NEWREMOVESTRING =  SUBSTRING(@GENERATED_SCRIPT, 1, LEN(@GENERATED_SCRIPT) - 2) + ' '  
SET @GENERATED_SCRIPT = @NEWREMOVESTRING + CHAR(10) + 'FROM dbo.' + @TABLENAME + ' WITH(NOLOCK)'
SELECT @GENERATED_SCRIPT
END
--PRINT @GENERATED_SCRIPT
END
END