Author |
Topic  |
|
mohan123
Constraint Violating Yak Guru
India
252 Posts |
Posted - 02/12/2013 : 06:46:58
|
hi all,
I had a Database in which i need to delete all the transaction (child) tables except Master tables . In that only Master tables remain and rest of the child tables need to be deleted. How i should do this one suggest me
P.V.P.MOhan |
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 02/12/2013 : 07:48:27
|
Is there a systematic way of identifying child tables? Via a naming convention perhaps, or there are foreign key relationships from the master tables? If for example, all child tables end with the word "Detail", you can run the following query, copy the results to a query window, verify that that is what you want to do and the run it:SELECT 'TRUNCATE TABLE '+ QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME) TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%DETAIL' |
 |
|
mohan123
Constraint Violating Yak Guru
India
252 Posts |
Posted - 02/12/2013 : 08:06:15
|
In Entire Database i need to delete all child tables in one shot and master tables should remain in database and you are saying need to give table name and truncate. But i have around 600 tables in which of them 120 are master tables. It is highly difficult for me to give every other table name
P.V.P.MOhan |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 02/12/2013 : 08:08:58
|
once check this query for listing child table names
;with cteTbHierarchy
as ( /* Select all table without (selfreferencing) FK */
select distinct
1 as LevelID
, OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
, Parent.name as TableName
, Parent.object_id as TbObjID
from sys.objects Parent
left join sys.foreign_key_columns RefKey
On RefKey.parent_object_id = Parent.object_id
and RefKey.parent_object_id <> RefKey.referenced_object_id
and RefKey.constraint_column_id = 1
where RefKey.parent_object_id is null
and Parent.type = 'U'
and Parent.name <> 'dtproperties'
UNION ALL
/* add tables that reference the anchor rows */
SELECT H.LevelID + 1
, OBJECT_SCHEMA_NAME(Parent.object_id) as TableOwner
, OBJECT_NAME(Parent.object_id) as TableName
, Parent.object_id as TbObjID
from sys.objects Parent
inner join sys.foreign_key_columns RefKey
On RefKey.parent_object_id = Parent.object_id
and RefKey.parent_object_id <> RefKey.referenced_object_id
and RefKey.constraint_column_id = 1
inner join cteTbHierarchy H
on H.TbObjID = RefKey.referenced_object_id
where Parent.type = 'U'
and Parent.name <> 'dtproperties'
)
select distinct LevelID, TableOwner, TableName
from cteTbHierarchy
WHERE LevelID!=1
order by LevelID desc -- descending order = order of row deletes
, TableOwner
, TableName ;
-- Chandu |
 |
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 02/12/2013 : 08:09:00
|
You won't have to manually type in the name of each child table if there is some way to programmatically identify a given table as being child table or master table.
Is there a systematic way to identify the child tables? Do all child tables have foreign key relationships with the master tables? |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 02/12/2013 : 08:21:21
|
Are you asking for transactional data(Child Table's Data) deletion or Droping Child Tables?
-- Chandu |
 |
|
mohan123
Constraint Violating Yak Guru
India
252 Posts |
Posted - 02/13/2013 : 01:21:08
|
yeah chandu yes deleting all the child tables not about dropping...atlast after running query there shoul be only master data
P.V.P.MOhan |
 |
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 02/18/2013 : 01:26:30
|
Hi Mohan,
See following code... It will generate TRUNCATE Statements for all Child Tables
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 = 'delete from ' + tablename + ' ; '
from
MyRecursion
group by
TableName
HAVING max(Lvl) >1
order by
1 desc
,2 desc;
-- Chandu |
Edited by - bandi on 02/18/2013 02:20:57 |
 |
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
|
|
Topic  |
|