Drop all databases in sql server

DECLARE @command NVARCHAR(MAX);
SET @command = N'';

SELECT @command
= @command
  + N'ALTER DATABASE [' + [name] + N']  SET single_user with rollback immediate;' + CHAR(13) + CHAR(10)
  + N'DROP DATABASE [' + [name] + N'];' + CHAR(13) + CHAR(10)
FROM [master].[sys].[databases]
WHERE [name] NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'ReportServer' );

SELECT @command;
--EXECUTE sp_executesql @command

Dynamically Generated Alter Table Script In Sql Server

ALTER PROCEDURE [dbo].[SP_Generate_AlterColumn]
AS
BEGIN
DECLARE @PRINT_RESULT VARCHAR(MAX)=''
DECLARE @COLUMN_EXIST VARCHAR(MAX)=''
DECLARE @TABLE_EXIST VARCHAR(MAX)=''

DECLARE db_cursor CURSOR FOR

SELECT OBJECT_NAME(sc.[object_id]),sc.[name] as [column]
FROM [sys].[columns] sc
JOIN [sys].[objects] so
ON sc.[object_id] = so.[object_id]
--WHERE  OBJECT_NAME(sc.[object_id])='TABLENAME'
ORDER BY so.modify_date DESC, so.create_date ASC

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @TABLE_EXIST,@COLUMN_EXIST 
IF  EXISTS
(
SELECT * FROM [sys].[columns] sc
JOIN [sys].[objects] so
ON sc.[object_id] = so.[object_id]
JOIN sys.tables ON  sc.object_id = tables.object_id
WHERE OBJECT_NAME(sc.[object_id])= @TABLE_EXIST AND sc.name = @COLUMN_EXIST
)
BEGIN
WHILE @@FETCH_STATUS = 0 
BEGIN 
 
  SET @PRINT_RESULT = 'ALTER TABLE ' + @TABLE_EXIST + ' ADD ' + @COLUMN_EXIST + ' '+
  (
SELECT temp.system_type_name FROM
(
SELECT * FROM sys.dm_exec_describe_first_result_set 
(
'SELECT * FROM ['+ @TABLE_EXIST +'] ', NULL, 0
)
) AS temp WHERE temp.name=@COLUMN_EXIST
  )
  FETCH NEXT FROM db_cursor INTO @TABLE_EXIST,@COLUMN_EXIST
  PRINT @PRINT_RESULT
END
END

CLOSE db_cursor 
DEALLOCATE db_cursor
END

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)