you could get the DB's by using:
select * from master.dbo.sysdatabases where dbid > 4
You could then loop through the objects with:
SELECt CASE WHEN xtype = 'P' THEN 'DROP PROC ' + name
WHEN xtype = 'U' THEN 'DROP TABLE ' + name
END
from sysobjects
where xtype in( 'P','U')
Order by xtype, name
Depending on the objects you could also add functions..views..etc
|||Perhaps you might want to check the objects for not being system ones:where xtype in( 'P','U')
AND OBJECTPROPERTY(OBJECT_ID(name),'IsMSShipped') = 0
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I use the below script to clean up the objects in a database. The script will fail if you have schemabound dependencies between objects.
For e.g. if there is a schemabound view that is dependent on a table, dropping the table before dropping the view will fail and will be report by an appropriate error message. You will need to manually drop such objects.
DECLARE @.sqlcommand VARCHAR(max), @.delimiter CHAR, @.ErrorMessage NVARCHAR(4000)
SET @.delimiter = ';'
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'AF' THEN N'AGGREGATE'
WHEN 'P' THEN N'PROCEDURE'
WHEN 'PC' THEN N'PROCEDURE'
WHEN 'FN' THEN N'FUNCTION'
WHEN 'FS' THEN N'FUNCTION'
WHEN 'FT' THEN N'FUNCTION'
WHEN 'R' THEN N'RULE'
WHEN 'RF' THEN N'PROCEDURE'
WHEN 'SN' THEN N'SYNONYM'
WHEN 'IF' THEN N'FUNCTION'
WHEN 'TF' THEN N'FUNCTION'
WHEN 'U' THEN N'TABLE'
WHEN 'V' THEN N'VIEW'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','D','F','PK','UQ','X','S','IT','TR','TA','R','SQ') --Not dropping constraints,triggers,service queues
exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;
--Now lets drop the rules and defaults
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP '+
CASE type
WHEN 'R' THEN N'RULE'
WHEN 'D' THEN N'DEFAULT'
ELSE N'INVALID'
END
+' '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.objects
WHERE type not in ('C','F','PK','UQ','X','S','IT','TR','TA','SQ')
exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;
--Now lets drop the types
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP TYPE '+SCHEMA_NAME(schema_id)+'.'+name
FROM sys.types
WHERE system_type_id <> user_type_id and name <> 'sysname'--only drops user defined types
exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;
--Now lets drop the schemas
begin try
begin transaction
SELECT @.sqlcommand = COALESCE(@.sqlcommand + @.delimiter, '') + ' DROP SCHEMA '+name
FROM sys.schemas where schema_id > 4 and schema_id < 16384
exec (@.sqlcommand);
commit transaction
end try
begin catch
rollback transaction
SELECT ERROR_MESSAGE()
end catch
set @.sqlcommand=null;
|||
We are on track. Basically I am trying to do something like this:
create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))
insert into #dbs (dbname)
select name from master.dbo.sysdatabases where dbid > 4
select * from #dbs
DECLARE @.sqlcommand VARCHAR(max),
@.useCommand varchar(max),
@.delimiter CHAR,
@.dbname nvarchar(128),
@.idx int
SET @.delimiter = ';'
select @.idx = min(dbid) from #dbs
while @.idx is not null
begin
select @.dbname = dbname from #dbs where dbid = @.idx
select @.useCommand = 'USE ' + @.dbname + @.delimiter
select @.useCommand
SELECT @.sqlcommand = COALESCE(quotename(@.sqlcommand), quotename(@.delimiter), ' ') + 'DROP SCHEMA ' + name
FROM quotename(@.dbname)+'.sys.schemas where schema_id > 4 and schema_id < 16384'+''''
select @.sqlcommand
--exec(@.sqlcommand)
select @.idx = min(dbid) from #dbs where dbid > @.idx
select @.sqlcommand
end
My code is just not working because of syntax errors. I think I am very close though, maybe one you SQL experts out there can show me my mistake and help me out. Thanks for all your help thus far!
|||Maybe this isn't even possible, if its not please let me know so I stop spinning my wheels and trying to figure this one out.|||This can get really complicated because you cannot drop the schemas without first dropping the objects in that schema. Is it too much to run the script manually per database ?|||The script is for a migration from a SQL Server 2000 databse to SQL Server 2005. All of the objects are owned by dbo. The schemas are automatically created for each user as part of the migration from SQL Server 2000 to 2005. We are just doing a back up and restore of the database to migrate it. I need to drop all the schemas in each database before I can drop and re add all the users. Everything needs to be scripted and automated so it can be tested before running on production.
I have come up with this thus far:
/* Drop schemas */
create table #dbs (dbid int IDENTITY(1,1), dbname nvarchar(128))
insert into #dbs (dbname)
select name from master.dbo.sysdatabases where dbid > 4
create table #schemas (schemaid int identity(1,1), dbname nvarchar(max), schemaname nvarchar(max))
/* Change the object owner for these objects temporarily so we can drop the schema */
select @.idx = min(dbid) from #dbs
while @.idx is not null
begin
select @.dbname = dbname from #dbs where dbid = @.idx
select @.sql = 'INSERT INTO #schemas (dbname,schemaname) '
select @.sql = @.sql + 'Select ' +''''+@.dbname+'''' + ', name From ' + quotename(@.dbname) + '.sys.schemas where schema_id > 4 and schema_id < 16384'
Exec(@.sql)
select @.idx = min(dbid) from #dbs where dbid > @.idx
end
select @.idx = min(schemaid) from #schemas
while @.idx is not null
begin
select @.sql = ' USE ' + quotename(dbname) + ' DROP SCHEMA ' + schemaname + ';' from #schemas where schemaid = @.idx
Exec(@.sql)
select @.idx = min(schemaid) from #schemas where schemaid > @.idx
end
