The stored procedure obtains the referenced information of the table.

It may be difficult to obtain a question, but I will know what it means after reading the article.
When you want to delete a table, it may be referenced by other tables as foreign keys. In this case, you cannot delete the table. You must delete the table that references this table as a foreign key to delete the table you want to delete. It may be hard to express the text. Let me give you an example.
When table B and Table C Reference Table A as the foreign key and you want to delete some data in Table A, you must delete the data in Table B and Table C that references Table. However, table B and Table C may be referenced by other tables, which will certainly lead to a recursion.
If you delete table A in the normal way, you may write a delete statement to delete table A, but an error will be reported during execution that the table references this table, at this time, you will find the referenced table based on the error message, delete the table, report an error, and search for the table based on the information. At this time, I feel very troublesome.
I wrote a stored procedure myself.

It is reasonable to say that it is best to use recursion to obtain the information of the referenced table, but I have tried to use C # in the database to change the policy and use the process for writing.

The Code is as follows:

 

 

 

Code
Create procedure DBO. usp_getfeferencetableinfo
@ Tablename nvarchar (50)
As

Set nocount on
Create Table # foreignkeytableinfo
(
Pktable_qualifier sysname collate database_default null,
Pktable_owner sysname collate database_default null,
Pktable_name sysname collate database_default not null,
Pkcolumn_name sysname collate database_default not null,
Fktable_qualifier sysname collate database_default null,
Fktable_owner sysname collate database_default null,
Fktable_name sysname collate database_default not null,
Fkcolumn_name sysname collate database_default not null,
Key_seq smallint not null,
Update_rule smallint null,
Delete_rule smallint null,
Fk_name sysname collate database_default null,
Pk_name sysname collate database_default null,
Deferrability smallint null
)
Declare @ level int
Declare @ parentgetsubfornull int
Declare @ SQL nvarchar (max)
Set @ level = 0
Set @ parentgetsubfornull = 1000

While @ parentgetsubfornull <> 0
Begin
Print @ level
If @ level <> 0
Begin
Set @ SQL =''
Select @ SQL = @ SQL + 'insert into # foreignkeytableinfo exec sp_fkeys '+ fktable_name + ''+ char (13) + char (10) from # foreignkeytableinfo where update_rule = @ level
Exec (@ SQL)
End
Else
Insert into # foreignkeytableinfo exec sp_fkeys @ tablename

Select @ parentgetsubfornull = count (*) from # foreignkeytableinfo where key_seq = 1

If exists (select 1 from # foreignkeytableinfo where key_seq = 1)
Begin
Update # foreignkeytableinfo set update_rule = @ LEVEL + 1 where key_seq = 1
Update # foreignkeytableinfo set key_seq = 2
End

Set @ level = @ LEVEL + 1

End
 
Select pktable_name, pkcolumn_name, [level] = update_rule, fk_name, pk_name from # foreignkeytableinfo

Set nocount off

The returned result contains several fields. level indicates the depth of the table to be referenced, that is, B, c references a, d References B, and f references C. Then the depth of Table B and Table C is 1, the depth of Table D and table F is 2. in this case, you can delete a table directly from D, F, and from the depth to the lower. Another field is pk_name, which indicates that the tables to be deleted are referenced. Other fields should be clear.

I don't know who can implement this function recursively.