Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-02-12 : 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 meP.V.P.MOhan |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-12 : 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.TABLESWHERE TABLE_NAME LIKE '%DETAIL' |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-02-12 : 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 nameP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-12 : 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-12 : 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
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-12 : 08:21:21
|
Are you asking for transactional data(Child Table's Data) deletion or Droping Child Tables?--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-02-13 : 01:21:08
|
yeah chandu yes deleting all the child tables not about dropping...atlast after running query there shoul be only master dataP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-18 : 01:26:30
|
Hi Mohan,See following code... It will generate TRUNCATE Statements for all Child Tableswith 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 MyRecursiongroup by TableNameHAVING max(Lvl) >1order by 1 desc ,2 desc; --Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|