July 9, 2013 at 12:12 pm
I am not a DBA, but an application developer. After I ran some database creation script against my SQL Server Express 2012, I notice that all of my database objects are created in the master database, maybe due to the fact that my script has the following:
[font="Courier New"]USE [master]
GO
CREATE DATABASE [MyApplicationDb] ON PRIMARY
( NAME = N'MyDb', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012EXPRESS\MSSQL\DATA\MyApplicationDb.mdf' , SIZE = 323584KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDb_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SS2012EXPRESS\MSSQL\DATA\MyApplicationDb.ldf' , SIZE = 41024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
[/font]
Of course there are more scripts following this to create stored procedures, views, functions and insert data into the tables.
So, now, here it is. Everything is stuck into the master database, which sucks. So, how do I clean up my master database? I don't have a backup of this database. I guess I have to start querying the allobjects table and check if they are ms shipped? Any idea on how to clean up the master DB? Thank you.
Also, why does SQL Server create everything in the master DB given my script? Please help. Thanks a lot!
July 9, 2013 at 12:18 pm
sql_sabis (7/9/2013)
Also, why does SQL Server create everything in the master DB given my script? Please help. Thanks a lot!
Probably because you didn't have a USE MyApplicationDb between the CREATE DATABASE and the object creations. Hence since master was still the selected database all the objects went into master.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2013 at 12:23 pm
All of the objects where created in master because the very first statement in your script states "user master". That sets the database context on which database to use and will remain in that database until another "use" + database name is issued. If you wanted all of the objects to be created in the newly created database you would have to have another "use" + databasename prior to the object create statements. As far as cleaning up master goes, you'll need to query the sys.objects table and look for all objects with the created_date equal to the date you ran the script and then drop those objects.
July 9, 2013 at 12:23 pm
GilaMonster (7/9/2013)
sql_sabis (7/9/2013)
Also, why does SQL Server create everything in the master DB given my script? Please help. Thanks a lot!Probably because you didn't have a USE MyApplicationDb between the CREATE DATABASE and the object creations. Hence since master was still the selected database all the objects went into master.
Yes, that's probably the case, but then how do I clean up my master db? Also, very strangely, I don't see a Tables folder under my master database, unlike other regular databases. Yet, I know those tables are there in master, because I can run "SELECT * FROM Students" and get the resultset. Of course, wrong database, SQL Server!
July 9, 2013 at 12:27 pm
Richard Moore-400646 (7/9/2013)
All of the objects where created in master because the very first statement in your script states "user master". That sets the database context on which database to use and will remain in that database until another "use" + database name is issued. If you wanted all of the objects to be created in the newly created database you would have to have another "use" + databasename prior to the object create statements. As far as cleaning up master goes, you'll need to query the sys.objects table and look for all objects with the created_date equal to the date you ran the script and then drop those objects.
So, to check the Created_date is one way to go. Unfortunately, this is a huge database, with hundreds of tables + views + stored procedures + functions. Any idea about how to generate some script to get rid of those? Muck like
SELECT "DROP TABLE " + o.TABLE_NAME FROM sys.objects WHERE CREATED_DATE > '20130708'
Will that work?
July 9, 2013 at 12:41 pm
sql_sabis (7/9/2013)
Also, very strangely, I don't see a Tables folder under my master database, unlike other regular databases.
Right click the master database -> refresh. Object Explorer does not refresh automatically
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2013 at 12:45 pm
GilaMonster (7/9/2013)
sql_sabis (7/9/2013)
Also, very strangely, I don't see a Tables folder under my master database, unlike other regular databases.Right click the master database -> refresh. Object Explorer does not refresh automatically
Thanks, but no, that does not work. I tried it before I asked. This symptom also exists in another instance of SQL Server Express 2012 where I messed up the master db exactly the same way. No Tables folder in master, man, but the tables are there. Strange. Any other idea?
July 10, 2013 at 8:58 am
This Connect item suggests Microsoft acknowledges this as a bug and that it'll be fixed in the "next major release", whatever that is...
July 10, 2013 at 9:02 am
paul.knibbs (7/10/2013)
Thanks for demystifying it.
July 10, 2013 at 1:11 pm
You can try the following:
use master
EXEC sp_MSforeachtable 'DROP TABLE ?'
But be very careful!!! I tested this on a test server and it did get rid of tables that had accumulated over the last few months. Also, it did NOT touch the system tables, which was good. Always proceed with caution.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 10, 2016 at 9:39 am
I have created a small script that cleans up all the mess. You might need to run it a few times and/or extend it with your own objects. Make sure you set the date properly, otherwise it won't do anything.
DECLARE @date date
SELECT @date = '2099-01-01' -- Adjust to the date that you started the mess :-)
DECLARE @statement nvarchar(250)
-- Kill all processes that have a connection to this database
DECLARE ForeignKeyCursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT fk.name, tab.name
FROM sys.objects fk
INNER JOIN sys.objects tab ON fk.parent_object_id = tab.object_id
WHERE fk.TYPE = 'F'
DECLARE @keyName sysname
DECLARE @tableName sysname
OPEN ForeignKeyCursor
FETCH NEXT FROM ForeignKeyCursor INTO @keyName, @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = 'ALTER TABLE [' + @tableName + '] DROP CONSTRAINT [' + @keyName + ']'
EXEC sp_executeSql @statement
FETCH NEXT FROM ForeignKeyCursor INTO @keyName, @tableName
END
CLOSE ForeignKeyCursor
DEALLOCATE ForeignKeyCursor
-- Kill all processes that have a connection to this database
DECLARE ObjectCursor CURSOR LOCAL FORWARD_ONLY
FOR SELECT name, type FROM sys.objects WHERE create_date >= @date
DECLARE @name sysname
DECLARE @type nvarchar(10)
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @name, @type
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement =
CASE @type
WHEN 'FN' THEN 'DROP FUNCTION [' + @name + ']'
WHEN 'IF' THEN 'DROP FUNCTION [' + @name + ']'
WHEN 'TF' THEN 'DROP FUNCTION [' + @name + ']'
WHEN 'P' THEN 'DROP PROCEDURE [' + @name + ']'
WHEN 'U' THEN 'DROP TABLE [' + @name + ']'
WHEN 'V' THEN 'DROP VIEW [' + @name + ']'
ELSE null
END
IF @statement IS NOT NULL
EXEC sp_executeSql @statement
FETCH NEXT FROM ObjectCursor INTO @name, @type
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO
Unfortunately, I couldn't find a way to safely detect if a user-defined type could be deleted. You have to do this by hand.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply