query to delete all tables in a sqlserver database hosted

Posted by Parthibansk under Sql Server on 10/21/2013 | Points: 10 | Views : 1074 | Status : [Member] | Replies : 3
i have to remove all tables in a database hosted in server with constraints..

query below works fine locally..

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"


but not in hosted databasea




Responses

Posted by: Bandi on: 10/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Do you know one thing about sp_MSforeachtable ?
sp_MSforeachtable is un-documented stored procedure in SQL server.. Better not to use this in the production server...

If you have Foreign key relation ships among the tables...

Use the following query to get the script of DROP TABLE command in the hierarchical order of relations...
;with Fkeys as (
select distinct
OnTable = onTableSchema.name + '.' + OnTable.name
,AgainstTable = againstTableSchema.name + '.' + AgainstTable.name
from sysforeignkeys fk
inner join sys.objects onTable
on fk.fkeyid = onTable.object_id
inner join sys.objects againstTable
on fk.rkeyid = againstTable.object_id
inner join sys.schemas onTableSchema
on onTable.schema_id = onTableSchema.schema_id
inner join sys.schemas againstTableSchema
on againstTable.schema_id = againstTableSchema.schema_id
where 1=1
AND AgainstTable.TYPE = 'U'
AND OnTable.TYPE = 'U'
-- ignore self joins; they cause an infinite recursion
and onTableSchema.name + '.' + OnTable.name <> againstTableSchema.name + '.' + AgainstTable.name
)

,MyData as (
select
OnTable = s.name + '.' + o.name
,AgainstTable = FKeys.againstTable
from
sys.objects o
inner join sys.schemas s
on o.schema_id = s.schema_id

left join FKeys
on s.name + '.' + o.name = FKeys.onTable
left join Fkeys fk2
on s.name + '.' + o.name = fk2.AgainstTable
and fk2.OnTable = Fkeys.AgainstTable

where 1=1
and o.type = 'U'
and o.name not like 'sys%'
and fk2.OnTable is null
)

,MyRecursion as (

-- base case
select
TableName = OnTable
,Lvl = 1
from
MyData
where 1=1
and AgainstTable is null

-- recursive case
union all select
TableName = OnTable
,Lvl = r.Lvl + 1
from
MyData d
inner join MyRecursion r
on d.AgainstTable = r.TableName
)

select
Lvl = max(Lvl)
,TableName
,strSql = 'DROP TABLE ' + tablename + ' ; '
from
MyRecursion
group by
TableName
--HAVING max(Lvl) >1
order by
1 desc
,2 desc;


then execute the same script

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Are you trying to DELETE records from table or dropping tables in the database?

Which error you are getting by using sp_MSforeachtable ?

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/21/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
--Alternate is
1) get all table names in the hierarchical order by suing above posted query
2) get comma separated form for all table names and then use the following link
http://beyondrelational.com/modules/1/justlearned/0/tips/9120/you-can-drop-multiple-tables-using-single-drop-statement.aspx
3)execute the following statement to drop all tables in a database
DROP TABLE CommaSeparatedTableNames

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response