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!
Been poking around in sysobjects and information_schema.routines trying to work out how to best write scripts that will drop all specific objects from a database.
That is, scripts to drop all tables, views, stored procs, functions ( FN, IF, TF ) but can't seem to figure out appropriate way to do it.
A pointer on how to drop all of any one of the above object types would be greatly appreciated and I should be able to work out the others.
Further, when executing multiple scripts I am writing scripts like this...
ddl_batch.sql
Code Snippet
:R table1.sql
:R table2.sql
:R ufn_func1.sql
:R ufn_func2.sql
:R view_table1
:R view_table2
:R usp_proc1.sql
:R usp_proc2.sql
And executing via:
sqlcmd -S serverinstance -i ddl_batch.sql
This is to maintain individual object type scripts, and then to execute them together in dependency order, rather than executing one monolithic batch script. Is this a reasonable way to go about it or is there a better way?
I want to be able to drop a schema and all its objects if they exist. Can someone help me with such a stored procedure. I see the sql server does not allow dropping schema directly if it contains some objects.
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'. ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
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 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
I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete. Thanks, Steve
I am trying to drop a database, but keep getting the following error."cannot drop database 'blah' because it is currently being used forreplication".This db is not currently being replicated, but once was. It is thesubscriber side of an old replication pair.Can anyone tell what I have to do to make this go away?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hello everyone!I'm from El Salvador and i have a problem, i have a sybase systemrunning on windows 2000 server, probably a programmer drop adatabase...but i dont know who did it?, is it possible to know who dropthe database?, if the answer is yes... plese help me, because we need tosolve this security problem in our company.Sybase saves all the TSQL and transaction somewhere?,Saludos....--Posted via http://dbforums.com
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
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.
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.
Is there an easy way to add descriptions or comments to database objects? So, for instance, I might have a table that a number of analysts are using, and I'd like an easy way to save comments or descriptive data about each of the columns in the table. That way, all consumers of the DB can easily look up the meaning of each column, table, stored procedure, etc. I could maintain all of this information in a system separate from my RDBMS, but I'm wondering if there are existing ways to do this already built into SQL Server. I've seen something similar to this built into Oracle systems.
I would like to know if there would be any special way to force drop a database from an ASP.NET page.
When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout. In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.
I have a list of 35 tables that need to drop the primary key index from in my database.
My problem is as follows for these 35 tables:
1. How can I get a list of all the primary keys for this subset of tables in my database 2. How can I drop just the PK for each of these tables?
I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.
