SQL-Server query for results itself
I am trying to identify the risks when deleting tables from my database.
The database is large and contains hundreds of tables.
I have a query that returns foreign keys associated with a table. So I can feed in the names of the tables I want to drop and it tells me which tables rely on its columns. So I'll have to drop the returned tables as well.
My problem is that since I would then need to drop these tables, I would like to see any other tables that depend on the initial results.
As such, I believe I need my loop query to call itself with the results of the last run until there are no unique results.
Is it possible? Is there an easier way to do this?
EDIT: Here's the request:
SELECT
PK.TABLE_NAME AS PrimaryTable,
FK.TABLE_NAME AS ForeignTable,
PT.COLUMN_NAME AS PrimaryColumn,
CU.COLUMN_NAME AS ForeignColumn,
C.CONSTRAINT_NAME AS ConstraintName
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME IN
('Table1','Table2')
ORDER BY
1,2,3,4
source to share
You can try using CTE as such. I find it easier to write the actual where clause in the final select element, but if performance is an issue I would suggest writing the where clause in the relations
CTE (above UNION ALL)
CTE Select operation
;WITH q AS (
SELECT
PK.TABLE_NAME AS PrimaryTable,
FK.TABLE_NAME AS ForeignTable,
PT.COLUMN_NAME AS PrimaryColumn,
CU.COLUMN_NAME AS ForeignColumn,
C.CONSTRAINT_NAME AS ConstraintName
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
)
, Relations AS (
SELECT PrimaryTable AS Root
, *
FROM q
UNION ALL
SELECT r.Root
, q.PrimaryTable
, q.ForeignTable
, q.PrimaryColumn
, q.ForeignColumn
, q.ConstraintName
FROM q
INNER JOIN Relations r ON r.ForeignTable = q.PrimaryTable
)
SELECT *
FROM Relations
WHERE Root IN ('Table1','Table2')
ORDER BY
1,2,3,4
source to share
I think you could create a global temp table with fields for table names and a processed flag and add the name of the starting table. Call your code with the initial seed of the table you want to delete. Paste the results into the pace table. Then iterate over the values in the temp table, appending the results to the temp table, making sure you don't insert duplicates and label each one processed when it was done. Continue until every record in the table has been processed.
source to share
Try something like this.
DECLARE @tablename varchar
DECLARE @flag bit
SET @tablename = 'some_table_name'
SET @flag-1
WHILE EXISTS(SELECT PK.TABLE_NAME AS PrimaryTable,
FK.TABLE_NAME AS ForeignTable,
PT.COLUMN_NAME AS PrimaryColumn,
CU.COLUMN_NAME AS ForeignColumn . . . where PK.TABLE_NAME = @tablename)
begin
SET @flag=0
SET @tablename=<assign the tables with fk referred by primarykey tables>
end
IF(@flag=1)
<delete query>
Its just an idea to solve your scenario is not a complete solution.
source to share