Deleting All Data from a SQL Server Database: Listing 3
SELECT
db_name() as DatabaseName,
'[' + PTS.name + '].[' + PT.name + ']' as [ALTER TABLE],
CASE FK.is_not_trusted WHEN 1 THEN 'WITH NOCHECK' ELSE 'WITH CHECK' END as [WITH CHECK],
'[' + FK.name + ']' as [ADD CONSTRAINT],
'([' + FKK.name + '])' as [FOREIGN KEY],
FKC.Constraint_Column_id as Column_ID,
'[' + RTS.name +'].[' + RT.name +'] ([' + RCL.name + '])' as [REFERENCES],
CASE WHEN EP.Value is Null THEN ''
ELSE 'EXEC sys.sp_addextendedproperty ' +
'@name=''' + EP.[Name] +
''', @value=''' + CAST(EP.[value] as Varchar(Max)) +
''', @level0type=''schema'',@level0name=''' + FKS.name +
''', @level1type=''TABLE'', @level1name=''' + PT.name +
''', @level2type=''CONSTRAINT'',@level2name=''' + FK.name + ''''
END as [Extended Property],
FK.Object_ID as [Foreign Key Object ID],
FKS.name as [Foreign Key Schema Name],
'[' + FKS.name + '].[' + FK.name + ']' as [Foreign Key Constraint Name],
CASE FK.is_disabled WHEN 1 THEN 'True' ELSE 'False' END as Disabled,
CASE FK.is_not_for_replication WHEN 0 THEN 'True' ELSE 'False' END as [For Replication],
CASE FK.update_referential_action WHEN 1 THEN 'True' ELSE 'False' END as [Update References],
FK.Update_referential_action_desc as [Update Action],
CASE FK.delete_referential_action WHEN 1 THEN 'True' ELSE 'False' END as [Delete References],
FK.delete_referential_action_desc as [Delete Action],
IsNull(EP.name,'') as [Property Name],
IsNull(EP.value,'') as [Property_Value]
FROM sys.foreign_keys as FK
INNER JOIN sys.schemas as FKS on FK.schema_id = FKS.schema_id
INNER JOIN sys.tables as PT on FK.parent_object_id = PT.object_id
INNER JOIN sys.schemas as PTS on PT.schema_id = PTS.schema_id
INNER JOIN sys.foreign_key_columns as FKC on FK.Object_ID = FKC.constraint_object_id
INNER JOIN sys.columns as FKK
on FKC.parent_object_id = FKK.Object_id AND FKC.parent_column_id = FKK.column_id
INNER JOIN sys.tables as RT on FK.referenced_object_id = RT.object_id
INNER JOIN sys.schemas as RTS on RT.schema_id = RTS.schema_id
INNER JOIN sys.columns as RCL
on FKC.referenced_object_id = RCL.Object_id AND FKC.referenced_column_id = RCL.column_id
LEFT JOIN sys.extended_properties as EP on FK.Object_ID = EP.major_id
ORDER BY FKS.name + '.' + FK.name, FKC.Constraint_Column_id
About the Author
Joe Kunk is a Microsoft MVP in Visual Basic, three-time president of the Greater Lansing User Group for .NET, and developer for Dart Container Corporation of Mason, Michigan. He's been developing software for over 30 years and has worked in the education, government, financial and manufacturing industries. Kunk's co-authored the book "Professional DevExpress ASP.NET Controls" (Wrox Programmer to Programmer, 2009). He can be reached via email at [email protected].