Does anyone have a script that will iterate through a database and drop all objects in order of dependencies? I need a way recreate all objects in database through SQL script in a development environment without having to recreate the actual database. Therefore I need a way to clear out the current database objects first without running into a problem with dependencies. This needs to be dynamic so that if a object is added, it is automatically included in the drop scripts.
After I drop the database, I backed up the master database, I recreate the database with new name. When I tried to do the import on the command level, It gives me the error: Attempt to locate entry in the sysdatabases for database 'db1' by name, failed -- no entry found in that name.
I am trying to run a dymanic DROP DATABASE <dbname> command in a stored procedure called from ASP.NET. I typically connect from the .net using a SQL builtin account. I am getting this error:
System.Data.SqlClient.SqlException: Cannot drop the database 'db_testco', because it does not exist or you do not have permission.
The database is there for sure asI can browse it via the Studio manager. What permission do I need to set for my .net login to allow it to drop ? I tried adding the Delete permission to the database and still no go...
i would like to drop a distribution database after disabling both susbscriber and publisher .but i get a message saying distribution database is still in use .and as far as i can tell its there is no one using it .also stopped sql agent but no luck . and when i do select @@ servername i get servername is null how do i correct it .
Is there a way in SQL to drop any users from a database. I am trying to schedule some nightly DB maintenance and some users are still in there sometimes. I need to be able to kick the out to do dbcc checkdbs, etc. Is there a way for me to do this without stopping the SQL services?
I need to restore my database and I keep getting a database in use error. Can someone tell me the easiest way to drop all connections to a database and open it exclusively?
Also, I'm not sure where this connection is coming from, there is another database on the same machine that has user connected, can these connections be the problem?
I am trying to delete a database that currently subscribes for replication. The problem however, is that the publishing server no longer exists and I can't disable the subscription therefore I can't drop the database. When I try to disable the replication the server tries to communicate with the old server. Is there anyway to force the dropping of the database or to force the server to disable replication.
With some performance issues faced as I checked on Google came across Paul Randal's article on Logging & Recovery: URL...In this regard, and with some schema changes required, ended up with a decision to drop and re-create a database. What are the proper way to do the same and following queries:
A) What would be the recommended way to shift data from some tables of former database to that of the newer one (The latter ones created with the Script Table transaction)? B) Would a simple Drop Database completely delete the former database completely and clean up the memory (database pages etc.) or I need to take some additional steps for clearing up the space? C) Most of the data tables have been updated via loading the Bulk Insert commands time to time from CSV/flat files. Could that be somehow the cause of enlarging the the size of Database and Log?
Why in the hell doesn't SQL Server provide a facility for dropping anactive connection on an attached database in SQL Server Managementconsole? I can't detach an attached database because apparently thereis an active connection.I know you can use SSEUTIL but it seems like kluge for a poorlythought out function.Crazy
Hello,A while back I dropped a text column from a SQL Server 7 databaseroughly 3GB in size. I expected the size of the database to decreaseby around 1GB, but no change occurred. After searching usenet, Idiscovered that SQL Server 7 has no way of reclaiming that space, butthat there is some command that can be run in SQL Server 2000 thatwill reclaim it.I have since migrated this database to SQL Server 2000, and am nowtrying to figure out what that command is, but cannot locate anyusenet posts about it... also tried searching books online, but can'tfind anything that way either.Does anyone know what I should run?Thanks,Tom
I have a database consisting of two main tables and 12 sub tables.
This was leading to increase in database size. So we thought of storing the sub tables data in the main tables in form of xml in a column of varchar(2000) type.
So we created a new database that only had 2 tables and we stored the data of the sub tables in the new column of the main table.
Surprisingly we saw that the database size increased rather than decreasing .
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX) DECLARE @Handle AS INT DECLARE @TransactionCount AS INT SELECT @Config = ' <ConfigurationDirectory > <ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
I would be grateful if you could mail/point me to a TSQL script that can show me the Data Definitions of my SQLserver database objects. In fact, I have found a Visual Basic script that may be of help but it demands the installation of the Visual Basic software, which I do not possess. Thanks,
I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:
1. If I run this command
EXEC sp_MScheck_uid_owns_anything 5
I get this weird error message:
"The user owns objects in the database and cannot be dropped."
Not sure why that is since I'm just trying to list the objects, not drop them.
2. I tried running a simple query to get the objects from the system table instead:
SELECT * from [dbo].[sysobjects] where uid = 5
This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:
The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.
Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!
I have an ActiveX script which I need to convert to a T-SQL Stored Procedure. As a part of it, I need to loop through all the tables in a Database and check whether the particular Table/View exists? If doesn't exist, I need to create one with Primary Key, Foreign Keys, Indexes (Clustered & Non-Clustered), and Check Constraints.
Here is the ActiveX Script I have:
Code: Dim sNextMonthTable Dim oServerName Dim oTables Dim CheckDate sNextMonthTable = "Jan_2012"
There are 2 databases A and B.How can I create a procedure in database A so that the procedure works with contains tables in Database B. IS IT POSSIBLE
What is the best approach when transferring all database objects & data from a SQL Server 6.5 database to SQL Server 7.0 database running on different servers.
I am in the process of cleaning up the security on one of our production databases. SA is already the owner of the database.
On this SQL server, there is a login that has access to this database that needs to be removed. When I uncheck this users access to this database I receive the following error message: "Error 15183. The user owns objects in the database and cannot be dropped".
How can i find out what objects this logins has ownership of and how can I change the ownership of these objects to SA?
-- This stored procedure will let you search through your database -- to find various objects that contain a particular string. -- For example, you may want to see all tables and views that contain -- a particular column.
use master IF (object_id('sp_FindReferences') IS NOT NULL) BEGIN PRINT 'Dropping: sp_FindReferences' DROP procedure sp_FindReferences END PRINT 'Creating: sp_FindReferences' GO CREATE PROCEDURE sp_FindReferences ( @string varchar(1000) = '', @ShowReferences char(1) = 'N' ) AS /****************************************************************************/ /* */ /* TITLE: sp_FindReferences */ /* */ /* DATE: 18 February, 2004 */ /* */ /* AUTHOR: WILLIAM MCEVOY */ /* */ /****************************************************************************/ /* */ /* DESCRIPTION: SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */ /* */ /****************************************************************************/ set nocount on
declare @errnum int , @errors char(1) , @rowcnt int , @output varchar(255)
/****************************************************************************/ /* INPUT DATA VALIDATION */ /****************************************************************************/
/****************************************************************************/ /* M A I N P R O C E S S I N G */ /****************************************************************************/
-- Create temp table to hold results create table #Results ( Name varchar(55), Type varchar(12), DateCreated datetime, ProcLine varchar(4000) )
IF (@ShowReferences = 'N') BEGIN insert into #Results select distinct 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, '' from sysobjects SO join syscomments SC on SC.id = SO.id where SC.text like '%' + @string + '%' union select distinct 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, '' from sysobjects SO where SO.name like '%' + @string + '%' union select distinct 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, '' from sysobjects SO join syscolumns SC on SC.id = SO.ID where SC.name like '%' + @string + '%' order by 2,1 END ELSE BEGIN insert into #Results select 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, 'Proc Line' = text from sysobjects SO join syscomments SC on SC.id = SO.id where SC.text like '%' + @string + '%' union select 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, 'Proc Line' = '' from sysobjects SO where SO.name like '%' + @string + '%' union select 'Name' = convert(varchar(55),SO.name), 'Type' = SO.type, crdate, 'Proc Line' = '' from sysobjects SO join syscolumns SC on SC.id = SO.ID where SC.name like '%' + @string + '%' order by 2,1 END
IF (@ShowReferences = 'N') BEGIN select Name, 'Type' = Case (Type) when 'P' then 'Procedure' when 'TR' then 'Trigger' when 'X' then 'Xtended Proc' when 'U' then 'Table' when 'C' then 'Check Constraint' when 'D' then 'Default' when 'F' then 'Foreign Key' when 'K' then 'Primary Key' when 'V' then 'View' else Type end, DateCreated from #Results order by 2,1 END ELSE BEGIN select Name, 'Type' = Case (Type) when 'P' then 'Procedure' when 'TR' then 'Trigger' when 'X' then 'Xtended Proc' when 'U' then 'Table' when 'C' then 'Check Constraint' when 'D' then 'Default' when 'F' then 'Foreign Key' when 'K' then 'Primary Key' when 'V' then 'View' else Type end, DateCreated, ProcLine from #Results order by 2,1 END
drop table #Results
GO IF (object_id('sp_FindReferences') IS NOT NULL) PRINT 'Procedure created.' ELSE PRINT 'Procedure NOT created.' GO
We having the SQL SERVER 2000 Production server. We need to cofigure the alert on the production server that if any objects schema or SP got changed then server should fire the alert to all of DBA mailid.
Would anyone be able to tell me how to save a database and all its objects e.g. Procedures and views etc. onto a CD and then open it on another SQL Server? For SQL Server 7.0 and 2000.
Could not allocate space for objects in database 'abc' And I have added 1 GB(1024 MB) of free space to primary file system of 'abc'. However now the primary file system of 'abc' database is 120 GB and the file properties are : Automatically grow file is checked, By percent 1 and restrict file growth: 121024 MB Still the database is showing as space avialable is 0.00, the total size is : 132186 MB
As of now I have't got any other alert, Please let me know if I get in the near future how to proceed??
One DTS package is running contunuously on this DB
I have a little trouble with sql server 2005 express database:
customer need install new web application to hosting, but at hosting is currently exist other web application and it's using DB what I must use. DB contains a big number of tables, views, functions, etc.
I need delete all user objects from this DB, it must be as new created one.
Is any query whitch can do this?
PS: I know, best way for this is delete DB and create new one, but i haven't permissions for these.
Could not allocate space for objects in database 'abc' And I have added 1 GB(1024 MB) of free space to primary file system of 'abc'. However now the primary file system of 'abc' database is 120 GB and the file properties are : Automatically grow file is checked, By percent 1 and restrict file growth: 121024 MB Still the database is showing as space avialable is 0.00, the total size is : 132186 MB
As of now I have't got any other alert, Please let me know if I get in the near future how to proceed??
One DTS package is running contunuously on this DB
I am trying to register our CLR assembly as an unsafe assembly without having to make the database trustworthy. Since making the database is_trustworthy_on = 1 is not a best practice, and would cause some of our customers (and our development process) a little bit of grief..
The reason the assembly is 'Unsafe' is because it is calling the TimeZoneInfo class to convert between timezones, since we are not yet on UTC dates. We plan to in the future but that's a big project.
We are also not using the 'SQLCLR' but rather have written our own class library and just have a project reference to it, which works just the same, but we have found to be better for the C# programmers.
I am playing with signing the assembly using an SNK file and have figured out what I need to do, including 1) creating a master key, 2) creating an asymmetric key using the same SNK file that signed the assembly, 3) creating a login for the asymmetric key, and 4) granting unsafe assembly to the login.
When I do all that with straight SQL, it actually works! But I am having trouble fitting this into our SSDT world. Should I create a separate SSDT project for items #1 through #4 above, and reference it, and check 'Include composite objects' in our publishing options? As stated in this blog post though, I'm terrified of messing up the master database, and I'm not excited about the overhead of another project, a 2nd dacpac, etc.
[URL] ...
Since we do use a common set of deployment options in a deployment tool we wrote, which does set the 'block on data loss' to false, and the 'drop objects not in source' to true, b/c we drop tables all the time during the course of refactoring, etc.
I don't want to drop anything in master though, and I don't want to have to publish it separately if we didn't have to.
I suppose I could just have some dynamic SQL in a pre-deployment script that takes care of the master database, but I was trying to do it the 'right' way, in a declarative style, but struggling.
So, in short, what's the recommended approach for getting an 'unsafe' CLR assembly into an SSDT project?
The error that started all this was:
CREATE ASSEMBLY for assembly *** failed because assembly *** is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true:
the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
I have a database called sky and its tables, views, procs and functions owned by sky. I need to replicate the sky database to another server. I had problem because those objects have ownership sky not dbo. I can not change ownership when replicate the database. How do I replicate database objects that are not owned by dbo? Is this possible or I have to change ownership from sky to dbo before replicate the database?
Thank you very much for your input and suggestions.
