Drop All Database Objects

Feb 3, 2006

Does anyone happen to have a script that will drop all database objects?

I'm looking for something generic that will work with any SQL Server 2000 database. I can write one myself, but I thought I would check here first.

Thanks in advance

John

View 2 Replies


ADVERTISEMENT

Drop All Objects In Database?

Feb 14, 2006

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!


-Cliff

View 7 Replies View Related

Drop All Objects

Jul 18, 2007

HI,



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?



Many thanks in advance for your help.





View 3 Replies View Related

Drop Schema And Its Objects

May 9, 2007



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.

View 10 Replies View Related

How To Get Information About Create/drop Objects?

Oct 2, 2000

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)?

View 1 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

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

View 2 Replies View Related

ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.

Mar 7, 2008

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
 

View 1 Replies View Related

Coping Database Objects From One Database To Another Blank Database.

Aug 18, 2005

I want to create a duplicate database   in sql 2000 using asp.net from a webform
I created a database using CREATE DATABASE .......

But how to copy tables, views, stored procedures to newly created
database from old using asp.net from webform

Is there any another method to create a duplicate database with another name
from existing database on same server ?

View 5 Replies View Related

Putting Names Of Objects To Control-flow Loop Creating Objects

Dec 27, 2006

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

View 5 Replies View Related

SQL Server 2012 :: Select Data From XML - Objects Within Objects?

Nov 20, 2013

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"

[code]....

View 1 Replies View Related

A Script To Get De DDL Of My Database Objects

Mar 3, 2005

Hello,

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,

Albert.

View 2 Replies View Related

Loop Through Different Objects In A Database?

Dec 27, 2011

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"

[Code] .....

View 12 Replies View Related

Creating Objects In Other Database

Sep 25, 2005

Hi

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

vic


Vicky

View 1 Replies View Related

Trying To Drop A Database

Aug 31, 2000

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

View 1 Replies View Related

Drop Database

Nov 20, 2007

anyone know how to drop my databse on sql pleasee

charles

View 8 Replies View Related

Cannot Drop Database

Jul 23, 2005

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!

View 1 Replies View Related

Someone Drop A Database....

Jul 20, 2005

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

View 1 Replies View Related

Transfer Database Objects & Data 6.5 To 7.0

May 22, 2000

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.

Thanks in adavance for your tips
Cheers,
Phil

View 1 Replies View Related

Change Owner Of Database Objects

Feb 23, 2001

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?

Thanks in advance,

Philip Talavera

View 1 Replies View Related

How To Find Invalid Objects In Database

Apr 24, 2014

I need to find all the invalid objects means which will throw error on execution in a particular DB.

View 1 Replies View Related

Find Database Objects That Contain A String

May 5, 2006

-- 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)

select @errnum = 0 ,
@errors = 'N' ,
@rowcnt = 0 ,
@output = ''

/****************************************************************************/
/* 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

View 4 Replies View Related

Get Alert On Modification Of Database Objects

May 31, 2007

Hi All,

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.


Is there any way to sent the alert.

Thanks in advance.

BPG

View 10 Replies View Related

Saving A SQL Server Database And Objects

Aug 7, 2007

Hi

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.

Thanks

View 2 Replies View Related

Could Not Allocate Space For Objects In Database '

Dec 30, 2007

I have received below alert:

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

View 2 Replies View Related

Delete All Sql Database User Objects

Jan 3, 2007

Good afternoon,

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.

Thank's for reply.

View 3 Replies View Related

Could Not Allocate Space For Objects In Database

Dec 31, 2007

I have received below alert:

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

View 4 Replies View Related

Dropping All Database Objects Through Script

May 28, 2008

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.

View 5 Replies View Related

How To Declare Objects In Master Database

Jan 16, 2015

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.

View 4 Replies View Related

Replicate Database Objects That Are Not Dbo Ownership

Aug 25, 2006

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.

View 1 Replies View Related

Adding Descriptions To Database Objects

May 16, 2008

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.

Thanks!

View 2 Replies View Related

Drop All Tables In Database

Apr 25, 2007

what is the sql query to drop all tables in a database in sql server 2000

View 5 Replies View Related

Force Drop Database

Jul 9, 2004

Hi everybody,

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.



Thanks in advance

View 3 Replies View Related

How To Drop All PKs On Tables In Database?

Feb 1, 2007

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.

Thanks

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved