May 20, 2017 at 6:45 am
I want to remove all tables except two table i.e mytable1 and mytable2 in my db.
Is there any script to do this ?
May 20, 2017 at 7:32 am
If you want to do this once, all you have to do is query the DROP TABLE statements:
SELECT 'DROP TABLE ' + QUOTENAME(name) + '; '
FROM sys.objects
WHERE type = 'U'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;
If you need to do this inside a procedure, you can build a single string of SQL statements to drop them all.
DECLARE @strSQL Varchar(MAX) = '';
SELECT @strSQL = @strSQL + 'DROP TABLE ' + QUOTENAME(name) + '; '
FROM sys.objects
WHERE type = 'U'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;
SELECT @strSQL;
These are bare-bones queries and don't take dependencies into account, but they should get you going in the right direction.
May 20, 2017 at 8:06 am
Ed Wagner - Saturday, May 20, 2017 7:32 AM
SELECT 'DROP TABLE ' + QUOTENAME(name) + '; '
FROM sys.objects
WHERE type = 'U'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;
This is not dropping tables. Could you please verify at your end.
I'm using SQL Server 2008
May 20, 2017 at 9:50 am
Could anyone please check this out ?
May 20, 2017 at 10:39 am
Ed generated the string you have to execute.
in his example the last line is the command, it was up to you to execute itSELECT @strSQL;
--execute if you are absolutely positively sure it's correct
EXECUTE (strSQL)
[
Lowell
May 20, 2017 at 11:12 am
Lowell - Saturday, May 20, 2017 10:39 AMEd generated the string you have to execute.in his example the last line is the command, it was up to you to execute it
SELECT @strSQL;
--execute if you are absolutely positively sure it's correct
EXECUTE (strSQL)
[
Thanks, Lowell. I should have included that I was just generating the SQL to do the work. Whatever is generated still has to be executed.
May 20, 2017 at 11:21 am
is this the full script ?
DECLARE @strSQL Varchar(MAX) = '';
SELECT @strSQL = @strSQL + 'DROP TABLE ' + QUOTENAME(name) + '; '
FROM sys.objects
WHERE type = 'U'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;
EXECUTE (strSQL)
This also not running
May 20, 2017 at 11:51 am
"not running" doesn't tell us anything.
if you got a specific error, tell us the error.
if you ran the select, was it an empty string?
maybe you have schemas and you did not mention it, like HumanResources.Employees isntead of dbo.EmployeesDECLARE @strSQL Varchar(MAX) = '';
--SELECT quotename(schema_Name(schema_id)) + '.' + QUOTENAME(name) ,
SELECT @strSQL = @strSQL + 'DROP TABLE ' + quotename(schema_Name(schema_id)) + '.' + QUOTENAME(name) + '; '
--select *
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;
SELECT @strSQL;
Lowell
May 20, 2017 at 12:50 pm
Lowell - Saturday, May 20, 2017 11:50 AM"not running" doesn't tell us anything.
if you got a specific error, tell us the error.
if you ran the select, was it an empty string?
maybe you have schemas and you did not mention it, like HumanResources.Employees isntead of dbo.EmployeesDECLARE @strSQL Varchar(MAX) = '';--SELECT quotename(schema_Name(schema_id)) + '.' + QUOTENAME(name) ,
SELECT @strSQL = @strSQL + 'DROP TABLE ' + quotename(schema_Name(schema_id)) + '.' + QUOTENAME(name) + '; '
--select *
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;SELECT @strSQL;
As I said ..I'm running this in a New query window ...not inside a store proc
when I run this code .. I get this
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@strSQL".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@strSQL".
I'm already selecting the right schema in Management Studio.
Do you have any working solution to this ?
thanks
May 20, 2017 at 1:12 pm
spectra - Saturday, May 20, 2017 12:50 PMLowell - Saturday, May 20, 2017 11:50 AM"not running" doesn't tell us anything.
if you got a specific error, tell us the error.
if you ran the select, was it an empty string?
maybe you have schemas and you did not mention it, like HumanResources.Employees isntead of dbo.EmployeesDECLARE @strSQL Varchar(MAX) = '';--SELECT quotename(schema_Name(schema_id)) + '.' + QUOTENAME(name) ,
SELECT @strSQL = @strSQL + 'DROP TABLE ' + quotename(schema_Name(schema_id)) + '.' + QUOTENAME(name) + '; '
--select *
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;SELECT @strSQL;
As I said ..I'm running this in a New query window ...not inside a store proc
when I run this code .. I get thisMsg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 4
Must declare the scalar variable "@strSQL".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@strSQL".I'm already selecting the right schema in Management Studio.
Do you have any working solution to this ?
thanks
This query will generate the statements you need to execute to drop your tables. However, it seems like you don't understand it, so please be careful. You don't want to accidentally drop all your tables.
1. Your error about not being able to assign a default value tells me you may be running this in SQL 2005. Since you posted your question in a SQL 2008 forum, I presumed you were using SQL 2008. If you want to run it in SQL 2005, get rid of the default value and assign the empty string in a separate SET statement.
2.. You haven't changed the names of the tables you want to save from my original query. You're going to want to do this so you don't generate the DROP TABLE statements for the tables you want to keep.
It sounds like this is a one-time action. Maybe you should use this script instead, which includes Lowell's update to include the schema.
SELECT 'DROP TABLE ' + QUOTENAME(schema_name(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.tables
WHERE type_desc = 'USER_TABLE'
AND name NOT IN ('TableToSave1', 'TableToSave2')
ORDER BY name;
Make sure you change the names of the tables to the ones you want to keep. Then run the query and take a look at the results. Copy and paste them into your code window and run the statements you want. This is a more careful approach and won't run all your DROP TABLE statements at once. You'll get to see them individually.
IMPORTANT: If you don't put it into a transaction, there is no undo. The way to get your tables back is to restore from backup.
May 20, 2017 at 8:59 pm
worked fine....thanks
May 21, 2017 at 1:23 am
Since you want to drop all procs, views, and all but 2 tables, why not simply start with a new DB, and create the 2 tables. Data from just 2 tables can easily be migrated as well.
That said, I find it quite strange that somebody would want to be dropping that many objects.
May 21, 2017 at 7:47 am
I'm glad it worked for you.
May 23, 2017 at 2:01 am
Also you can open
1. Object Explorer Details
2.Navigate to Tables Folder under respective database in Object Explorer
3.Filter out the tables you dont want to delete using the filter option --> now you can see only tables which has to be deleted
3. Select all tables --> right click -->delete
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply