Hello,

Due to the problem of failed installation, I have about 127 tables in my Sql database which I need to delete so as to re-install the same set of tables again. How do I do this effectively without causing other problems in the database.

Thanks in anticipation.

Regards.

If you do not have any foreign key constraints, you may try with the below:

Hope you dont have any other dependencies...

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM sys.tables
SELECT @listStr

Exec('Drop table '+ @listStr)

Select * From sys.tables

• Proposed as answer by Shridhar J Joshi 2 hours 32 minutes ago

just use a catalog view and generate script and apply it

DECLARE @TableList varchar(max),@SQL nvarchar(max)

SET @TableList = STUFF((SELECT ',' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
FOR XML PATH('')),1,1,'')

SET @Sql = N'DROP TABLE ' + @TableList

EXEC sp_executesql @SQL,'@TableList varchar(max)',@TableList=@TableList

Hi

Better way delete database

and try to install it again

Mark as answer if you find it useful

Shridhar J Joshi

Hello,

Due to the problem of failed installation, I have about 127 tables in my Sql database which I need to delete so as to re-install the same set of tables again. How do I do this effectively without causing other problems in the database.

Thanks in anticipation.

Regards.

Failed installation of what SQL Server? I have never heard that due to failed installation you delete tables.If it is due to failed installation of some tables getting created by application you can truncate the tables and then drop it.It will be easy and quick.

http://msdn.microsoft.com/en-us/library/ms17757

Hello,

Thanks for the advice.

I have read through all of them and they have not fully captured all of my needs. I am using an Sql database on a hosted site for more than one application -that is I have subsites in subfolders. As a result I need to use the TRUNCATE statement with a WHERE condition. All the tables I want to delete are prefixed  with ag_ . From the link given by Shridar J Joshi 'http://msdn.microsoft.com/en-us/library/ms177570.aspx', I came across the TRUNCATE statement which is equivalent to a DELETE without a WHERE condition. I have seen the DELETE with a WHERE condition to remove rows. But I need to come up with a statement that would remove 'prefixed tables'.

I would appreciate further help in this area.

Regards

If you do not have any foreign key constraints, you may try with the below:

Hope you dont have any other dependencies...

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM sys.tables
SELECT @listStr

Exec('Drop table '+ @listStr)

Select * From sys.tables

• Proposed as answer by Shridhar J Joshi Saturday, February 15, 2014 8:57 AM

If you do not have any foreign key constraints, you may try with the below:

Hope you dont have any other dependencies...

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM sys.tables
SELECT @listStr

Exec('Drop table '+ @listStr)

Select * From sys.tables

• Proposed as answer by Shridhar J Joshi Saturday, February 15, 2014 8:57 AM

Now you are correct you mentioned failed installation in your question which was confusing.Can you run query in sys.sysobject to list out all tables

You cannot use where clause in truncate statement where clause can only be used with delete .Below is on untested approach

use database_name--db name in which you want to truncate tables
go
select  'truncate table'+ name from sys.sysobjects where name like 'Ag_%' and type='u'

I donr know about constraints if any because as you said they are tables created wrongly.

Same was given by Latheesh I guess

I have put this together from my research;

USE MY_DATABASE;
GO
DELETE FROM MY_DATABASE
WHERE TABLE_NAME = 'myprefix_%';
GO

Can  I get a confirmation that I have used the right syntax.

I do not have another database with tables prefixed with 'myprefix' in the site.

Regards,

PRAISE_HIM

I have put this together from my research;

USE MY_DATABASE;
GO
DELETE FROM MY_DATABASE
WHERE TABLE_NAME = 'myprefix_%';
GO

Can  I get a confirmation that I have used the right syntax.

I do not have another database with tables prefixed with 'myprefix' in the site.

Regards,

PRAISE_HIM

Are you not reading the post carefully how can you use delete command on database try with one I have posted.YOur systnax is not correct it would be better to truncate table and then drop it

Can you try the below:

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = +COALESCE(@listStr+'; Truncate Table ' ,'Truncate Table ') + Name
FROM sys.tables where name like  'myprefix_%'
--SELECT @listStr

Exec(@listStr)

hi Praise_Him

You got several great solutions above, for doing the operation (deleting) automatic for whole tables which their name start with "myprefix_". yet, it look you are looking for something else. I can get it, as you dont know what is done actually, then you afraid from executing those automatic queries.

The time you spent writing your posts, you could actually delete the tables manually one by one. As this is a one time action, maybe you will feel better, and it will be more fit to your case, to do it using the SSMS GUI. Using 127 mouse cliks you can do :-)

check this link (especially the section "Using SQL Server Management Studio"):
http://technet.microsoft.com/en-us/library/ms190616.aspx

* What works for one person is not safe/suitable to another person. None of us as DBAs will probably chose this solution but I really have a feeling this is the best for you at this point. Just delete the tables one by one :-)

* This will also be better in case there is other restriction as relations between the tables or other element in the database. working one by one if you get a specific problem you can come back and tell us what is the error message that did not let you delete a specific table.

I Hope is helpful :-)

** In any case make sure that you backup the database before you start!