68 lines
1.9 KiB
Transact-SQL
68 lines
1.9 KiB
Transact-SQL
-- Letörli az összes létezõ adatbázis objektumot (tábla, constraintek, indexek, tárolt eljárások, viewk), az adatbázis törlése nélkül
|
|
|
|
/* FK-k törlése */
|
|
DECLARE @sql nvarchar(max) = '';
|
|
|
|
SELECT @sql += 'ALTER TABLE [' + TABLE_NAME + '] DROP CONSTRAINT ' + CONSTRAINT_NAME + char(13) + char(10)
|
|
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
|
|
WHERE CONSTRAINT_NAME like 'FK[_]%' AND TABLE_SCHEMA != 'sys'
|
|
|
|
--SELECT @sql
|
|
EXEC sp_executesql @sql
|
|
GO
|
|
|
|
/*SP-k és FN-ek törlése */
|
|
DECLARE @sql nvarchar(max) = '';
|
|
|
|
SELECT @sql += 'DROP ' + ROUTINE_TYPE + ' [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + ']' + char(13) + char(10)
|
|
FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA != 'sys'
|
|
|
|
--SELECT @sql
|
|
EXEC sp_executesql @sql
|
|
GO
|
|
|
|
/*TABLE és VIEW törlések*/
|
|
DECLARE @sql nvarchar(max) = '';
|
|
|
|
SELECT @sql += 'DROP ' + CASE TABLE_TYPE WHEN 'BASE TABLE' THEN 'TABLE [' WHEN 'VIEW' THEN 'VIEW [' ELSE NULL END + TABLE_SCHEMA + '].[' + TABLE_NAME +']' + char(13) + char(10)
|
|
FROM INFORMATION_SCHEMA.TABLES
|
|
WHERE TABLE_SCHEMA != 'sys'
|
|
|
|
--SELECT @sql
|
|
EXEC sp_executesql @sql
|
|
GO
|
|
|
|
/*LOGINok törlése*/
|
|
DECLARE @sql nvarchar(max) = '';
|
|
|
|
SELECT @sql += 'DROP LOGIN [' + REPLACE([SCHEMA_NAME], '_schema', '_user]') + char(13) + char(10)
|
|
FROM INFORMATION_SCHEMA.SCHEMATA
|
|
WHERE SCHEMA_OWNER = 'dbo' AND [SCHEMA_NAME] <> 'dbo' AND [SCHEMA_NAME] <> 'sys'
|
|
|
|
SELECT @sql
|
|
EXEC sp_executesql @sql
|
|
GO
|
|
|
|
/*USERek és ROLE-ok törlése*/
|
|
/*DECLARE @sql nvarchar(max) = '';
|
|
|
|
SELECT @sql += 'DROP ' + CASE [type] WHEN 'S' THEN ' USER [' WHEN 'R' THEN 'ROLE [' ELSE NULL END + [name] + ']' + char(13) + char(10)
|
|
FROM sys.database_principals
|
|
WHERE principal_id > 4 AND is_fixed_role = 0 */
|
|
|
|
--SELECT @sql
|
|
--EXEC sp_executesql @sql
|
|
GO
|
|
|
|
/*SCHEMA törlése*/
|
|
DECLARE @sql nvarchar(max) = '';
|
|
|
|
SELECT @sql += 'DROP SCHEMA [' + [SCHEMA_NAME] + ']' + char(13) + char(10)
|
|
FROM INFORMATION_SCHEMA.SCHEMATA
|
|
WHERE SCHEMA_OWNER = 'dbo' AND [SCHEMA_NAME] <> 'dbo' AND [schema_name] != 'sys'
|
|
|
|
--SELECT @sql
|
|
EXEC sp_executesql @sql
|
|
GO
|
|
|