Deleting All Data from a SQL Server Database: Listing 3

	db_name() as DatabaseName, 
	'[' + + '].[' + + ']' as [ALTER TABLE],
	'[' + + ']' as [ADD CONSTRAINT],
	'([' + + '])' as [FOREIGN KEY],
	FKC.Constraint_Column_id as Column_ID,
	'[' + +'].[' + +'] ([' + + '])'  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=''' + +
	  		 ''', @level1type=''TABLE'', @level1name=''' + +
	  		 ''', @level2type=''CONSTRAINT'',@level2name=''' + + ''''
	END as [Extended Property],
	FK.Object_ID as [Foreign Key Object ID], as [Foreign Key Schema 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(,'') 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 + '.' +, 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].

comments powered by Disqus


  • Python in VS Code Adds Data Viewer for Debugging

    The January 2021 update to the Python Extension for Visual Studio Code is out with a short list of new features headed by a data viewer used while debugging.

  • GitHub Ships Enterprise Server 3.0 Release Candidate

    It's described as "the biggest ever change to Enterprise Server," with improvements to Actions, Packages, mobile, security and more.

  • Attacks on .NET Apps Grow in Number, Severity, Says Security Firm

    .NET apps were found to have more serious vulnerabilities and suffer more attacks last year, according to data gathered by Contrast Labs.

  • Microsoft Opens Up Old Win32 APIs to C# and Rust, More Languages to Come

    Microsoft is opening up old Win32 APIs long used for 32-bit Windows programming, letting coders use languages of their choice instead of the default C/C++ option.

Upcoming Events