A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped.
This code can be used to drop Login from SQL Server and user name associated with this Login in different databases.
------------------------------------
-- Drop user - SQL Script --
------------------------------------
--
-- Login name to delete
DECLARE @userToDelete nvarchar(200) = 'TestLogin';
--
--
IF (NOT EXISTS (SELECT * FROM sys.syslogins AS L WHERE L.loginname = @userToDelete))
BEGIN
RAISERROR('
Login not found.
', 50000, 0) WITH log
END
DECLARE @sql nvarchar(max);
PRINT '-- CHECK Databases'
USE [master];
SET @sql = '';
select @sql=@sql+'USE ['+d.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;'+CHAR(13) from sys.sysdatabases d left join master.sys.syslogins l on d.sid = l.sid WHERE l.name = 'TestLogin';
PRINT @sql;
EXEC (@sql);
PRINT '-- CHECK Jobs'
USE [master];
SET @sql = '';
select @sql=@sql+'EXEC msdb.dbo.sp_update_job @job_name=N'''+s.name+''', @owner_login_name=N''sa'';'+CHAR(13) from msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid WHERE l.name = @userToDelete
PRINT @sql;
EXEC (@sql);
PRINT '-- CHECK Schemas'
SET @sql = '
DECLARE @sql nvarchar(max);
SET @sql = '''';
SELECT @sql=@sql+''ALTER AUTHORIZATION ON SCHEMA::['' + s.name + ''] TO [dbo];''+CHAR(13) FROM [?].sys.schemas AS s INNER JOIN [?].sys.sysusers AS u ON s.principal_id = u.uid WHERE (u.name = '''+@userToDelete+''')
IF NOT (@sql='''')
BEGIN
SET @sql = ''USE [?]''+CHAR(13)+@sql+''DROP USER ['+@userToDelete+']''+CHAR(13);
PRINT @sql;
EXEC (@sql);
END
'
exec sp_MSforeachdb @sql;
PRINT '-- CHECK Connections'
USE [master];
SET @sql = '';
select @sql = @sql + 'KILL ' + cast(spid AS NVARCHAR(10)) + ';'+ char(13) FROM master..sysprocesses Where loginame = @userToDelete;
PRINT @sql;
EXEC (@sql);
PRINT '-- DROP Login'
SET @sql = 'DROP LOGIN ['+@userToDelete+']'+CHAR(13);
PRINT @sql;
EXEC (@sql);
GO
source: https://msdn.microsoft.com/en-us/library/ms188012.aspx
Enable broker service on all databases but it check if Service Broker is enabled.
-- ***************************************** --
-- SQL Script to ENABLE_BROKER
-- ***************************************** --
--
PRINT @@servername;
PRINT Getdate();
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
--#region Select databases to Enable Broker Service
SELECT [name] INTO #dbs
FROM sys.databases
WHERE
database_id > 4
AND is_broker_enabled = 0 -- check if Service Broker is enabled.
AND name NOT IN ('ReportServer','ReportServerTempDB')
--#endregion Select databases to Enable Broker Service
USE [MASTER];
SET NOCOUNT ON;
DECLARE @name nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);
WHILE (EXISTS(SELECT * FROM #dbs))
BEGIN
SELECT TOP 1 @name = name
FROM #dbs;
DELETE FROM #dbs
WHERE
@name = name;
DECLARE @msg nvarchar(200) = '-- SET ENABLE_BROKER Database : ' + @name;
RAISERROR (@msg, 10, 1) WITH NOWAIT;
SET @ToKill = '';
SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
FROM master..sysprocesses
WHERE dbid = DB_ID(@name);
RAISERROR (@tokill, 10, 1) WITH NOWAIT;
EXEC (@tokill);
SET @sql = 'ALTER DATABASE [' + @name + '] SET ENABLE_BROKER WITH NO_WAIT;'
RAISERROR (@sql, 10, 1) WITH NOWAIT;
EXEC (@sql);
END
Lire la suite de Enable broker service on all databases
We have many ways to drop a database, but if you want to drop many databases.
You can have some problems with opened connections, but this script kill all connection and drop the database.
-- ***************************************** --
-- SQL Script to drop databases
-- ***************************************** --
--
PRINT @@servername;
PRINT Getdate();
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
--#region Select databases to DROP
SELECT [name] INTO #dbs
FROM sys.databases
WHERE
database_id > 4
AND name NOT IN ('ReportServer','ReportServerTempDB')
--#endregion Select databases to DROP
--#region Drop databases
USE [MASTER];
SET NOCOUNT ON;
DECLARE @name nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);
WHILE (EXISTS(SELECT *
FROM #dbs))
BEGIN
SELECT TOP 1 @name = name
FROM #dbs;
DELETE FROM #dbs
WHERE
@name = name;
DECLARE @msg nvarchar(200) = '-- DROP Database : ' + @name;
RAISERROR (@msg, 10, 1) WITH NOWAIT;
SET @ToKill = '';
SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
FROM master..sysprocesses
WHERE dbid = DB_ID(@name);
RAISERROR (@tokill, 10, 1) WITH NOWAIT;
EXEC (@tokill);
SET @sql = 'ALTER DATABASE [' + @name + '] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;'
RAISERROR (@sql, 10, 1) WITH NOWAIT;
EXEC (@sql);
SET @sql = 'DROP DATABASE [' + @name + ']';
RAISERROR (@sql, 10, 1) WITH NOWAIT;
EXEC (@sql);
END
--#endregion Drop databases
sources:
http://stackoverflow.com/questions/349358/sql-server-print-output-doesnt-appear-immediately
http://stackoverflow.com/questions/5777483/drop-all-databases-from-server
Unused indexes should be deleted, but you would not drop all the unused indexes without deep analyse.
This a script delete all unused indexes.
- Nonclustered indexes
- Non primary keys
- Non unique
- Non unused (#Total reads < 100)
Finding Unused Indexes -- Only Nonclustered
SELECT
db_name() as DatabaseName
,sys.schemas.NAME AS ShemaName
,sys.tables.NAME AS TableName
,sys.indexes.NAME AS IndexName
,8 * SUM(sys.allocation_units.used_pages) AS IndexSize
,sys.dm_db_index_usage_stats.user_updates AS 'Total Writes'
,sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups AS 'Total Reads'
,sys.dm_db_index_usage_stats.user_updates - (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) AS 'Difference'
,'USE '+ db_name()+';' + 'DROP INDEX '+QUOTENAME(sys.schemas.NAME)+ '.' + QUOTENAME(sys.tables.NAME) + '.' +QUOTENAME(sys.indexes.NAME) + ';' + CHAR(13) + CHAR(10) + 'GO'
FROM sys.partitions
INNER JOIN sys.indexes ON sys.partitions.index_id = sys.indexes.index_id
AND sys.partitions.object_id = sys.indexes.object_id
INNER JOIN sys.schemas
INNER JOIN sys.tables ON sys.schemas.schema_id = sys.tables.schema_id ON sys.partitions.object_id = sys.tables.object_id INNER JOIN sys.allocation_units ON sys.partitions.partition_id = sys.allocation_units.container_id INNER JOIN sys.dm_db_index_usage_stats ON sys.indexes.object_id = sys.dm_db_index_usage_stats.object_id
AND sys.indexes.index_id = sys.dm_db_index_usage_stats.index_id
WHERE
sys.indexes.is_primary_key = 0 -- non primary key
AND sys.indexes.is_unique = 0 -- non unique
AND sys.indexes.type in (2, 6, 7) -- Only Nonclustered -- https://msdn.microsoft.com/en-us/library/ms173760.aspx
AND (sys.dm_db_index_usage_stats.user_seeks + sys.dm_db_index_usage_stats.user_scans + sys.dm_db_index_usage_stats.user_lookups) < 100 -- unused !!!
GROUP BY
sys.schemas.NAME
,sys.tables.NAME
,sys.indexes.NAME
,sys.dm_db_index_usage_stats.user_seeks
,sys.dm_db_index_usage_stats.user_scans
,sys.dm_db_index_usage_stats.user_lookups
,sys.dm_db_index_usage_stats.user_updates
ORDER BY IndexSize DESC;
Lire la suite de Unused indexes
