更新时间:2023-02-02 23:29:02
已解决.首先,我得到有关FK的sysinfo.像这样:
Solved. First i get sysinfo about FK. Like this:
SELECT A.FOREIGN_KEY_NAME, B.TABLE_NAME, B.COLUMN_NAME, A.REFERENCED_TABLE, A.REFERENCED_COLUMN FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS B RIGHT OUTER JOIN (SELECT DISTINCT so.name AS FOREIGN_KEY_NAME, OBJECT_NAME(sf.rkeyid) AS REFERENCED_TABLE, sc2.name AS REFERENCED_COLUMN FROM sysobjects AS so INNER JOIN sysforeignkeys AS sf ON so.id = sf.constid INNER JOIN syscolumns AS sc1 ON sf.fkeyid = sc1.id INNER JOIN syscolumns AS sc2 ON sf.rkeyid = sc2.id WHERE (so.xtype = ''F'') AND (OBJECT_NAME(sf.rkeyid) = ''ESTADO'') AND (sc2.name = ''ESTADO'')) AS A ON B.CONSTRAINT_NAME = A.FOREIGN_KEY_NAME
然后,对于目录的每一行,我检查它是好"还是坏",如果不好,那么我会进行foreach循环:
Then, for every row of my catalog i check if it''s "good" or "bad", if it''s bad, than i make foreach cycle:
foreach (DataRow DR_fk in DT_state_relation.Rows)
{
cmd = new SqlCommand("UPDATE bts." + DR_fk["TABLE_NAME"] + " SET " + DR_fk["COLUMN_NAME"] + " = 99 WHERE " + DR_fk["COLUMN_NAME"] + " = " + DR["ESTADO"], con);
cmd.ExecuteNonQuery();
}
cmd = new SqlCommand("DELETE FROM bts.ESTADO WHERE ESTADO = " + DR["ESTADO"]);
cmd.ExecuteNonQuery();