Working with all databases (edit)

ThinkPad T440s: 9.500.000đ

https://laptoptcc.com/cua-hang/laptop-cu-lenovo-thinkpad-t440s-i5-4300u-ram-4gb-ssd-120gb-14-hd-1600x900-card-on/

How to change owner database SQL Server

https://blog.sqlauthority.com/2017/04/09/change-owner-database-sql-server-interview-question-week-116/

Script to create all databases

https://dba.stackexchange.com/questions/193976/generate-create-database-for-attach-for-all-existing-user-databases

;WITH CTE_Databases AS
 (
 SELECT D.name AS DatabaseName
 , D.database_id
 FROM sys.databases AS D
 WHERE D.name <> 'tempdb'
 AND NOT(D.name IN ('master', 'msdb', 'model'))
 )
, CTE_FirstAndLast AS
 (
 SELECT 1 AS CommandStep
 , 0 AS SubStep
 , D.database_id AS DatabaseID
 , D.DatabaseName AS DatabaseName
 , 'IF NOT EXISTS (SELECT TOP 1 1 FROM sys.databases WHERE name = ' + QUOTENAME(D.DatabaseName, '''') + ')' AS CommandText
 FROM CTE_Databases AS D
 UNION ALL
 SELECT 2 AS CommandStep
 , 0 AS SubStep
 , D.database_id AS DatabaseID
 , D.DatabaseName AS DatabaseName
 , 'BEGIN' AS CommandText
 FROM CTE_Databases AS D
 UNION ALL
 SELECT 3 AS CommandStep
 , 0 AS SubStep
 , D.database_id AS DatabaseID
 , D.DatabaseName AS DatabaseName
 , ' CREATE DATABASE ' + QUOTENAME(D.DatabaseName) + ' ON ' AS CommandText
 FROM CTE_Databases AS D
 UNION ALL
 SELECT 20 AS CommandStep
 , 0 AS SubStep
 , D.database_id AS DatabaseID
 , D.DatabaseName AS DatabaseName
 , ' FOR ATTACH;' AS CommandText
 FROM CTE_Databases AS D
 UNION ALL
 SELECT 90 AS CommandStep
 , 0 AS SubStep
 , D.database_id AS DatabaseID
 , D.DatabaseName AS DatabaseName
 , 'END' AS CommandText
 FROM CTE_Databases AS D
 UNION ALL
 SELECT 91 AS CommandStep
 , 0 AS SubStep
 , D.database_id AS DatabaseID
 , D.DatabaseName AS DatabaseName
 , 'GO' AS CommandText
 FROM CTE_Databases AS D
 UNION ALL
 SELECT 92 AS CommandStep
 , 0 AS SubStep
 , D.database_id AS DatabaseID
 , D.DatabaseName AS DatabaseName
 , '' AS CommandText
 FROM CTE_Databases AS D
 )
, CTE_Files AS
 (
 SELECT 10 AS CommandStep
 , ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) AS SubStep
 , F.database_id AS DatabaseID 
 , D.DatabaseName 
 , CASE WHEN ROW_NUMBER() OVER (PARTITION BY F.database_id ORDER BY F.file_id) > 1 THEN ' , ' ELSE ' ' END + '(FILENAME = N' + QUOTENAME(F.physical_name, '''') + ')' AS CommandText
 FROM sys.master_files AS F
 INNER JOIN CTE_Databases AS D ON d.database_id = F.database_id 
 )
, CTE_AllTogether AS 
 (
 SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
 FROM CTE_FirstAndLast
 UNION ALL
 SELECT CommandStep, SubStep, DatabaseID, DatabaseName, CommandText
 FROM CTE_Files
 )
SELECT * FROM CTE_AllTogether
ORDER BY DatabaseID, DatabaseName, CommandStep, SubStep;

Backup all databases automatically

https://solutioncenter.apexsql.com/how-to-backup-multiple-sql-server-databases-automatically/

http://www.techbrothersit.com/2016/04/how-to-drop-or-delete-all-user.html

https://devblogs.microsoft.com/scripting/use-powershell-to-script-sql-database-objects/

Backup all databases

https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

All permissions of an user

https://sqlgeekspro.com/query-to-retrieve-all-permissions-for-a-user-in-selective-databases/

https://sqlgeekspro.com/users-db_owner-access-databases/

Get Size of All Databases in MB and GB

https://sqlconjuror.com/sql-server-get-size-of-all-databases-in-mb-and-gb/

Drop all databases

https://stackoverflow.com/questions/5777483/drop-all-databases-from-server

EXEC sp_MSforeachdb '
IF DB_ID(''?'') > 4
BEGIN
EXEC(''
ALTER DATABASE [?] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [?]
'')
END'

Drop all databases

https://stackoverflow.com/questions/5116296/how-to-drop-multiple-databases-in-sql-server

use master
go
declare @dbnames nvarchar(max)
declare @statement nvarchar(max)
set @dbnames = ''
set @statement = ''
select @dbnames = @dbnames + ',[' + name + ']' from sys.databases where name not like '412687'
if len(@dbnames) = 0
 begin
 print 'no databases to drop'
 end
else
 begin
 set @statement = 'drop database ' + substring(@dbnames, 2, len(@dbnames))
 print @statement
 --exec sp_executesql @statement
 end