T-SQL游标+动态SQL (引用特定主键的行数)
T-SQL游标+动态SQL (引用特定主键的行数)
提问于 2019-09-27 22:40:35
我必须计算一个键在表中被使用了多少次。我有以下代码来获取引用该键的所有表：
SELECT s.SCHEMA_NAME,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
OUTER APPLY
(
SELECT i.SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA i
INNER JOIN SYS.TABLES s
ON i.SCHEMA_NAME = SCHEMA_NAME(s.SCHEMA_ID)
WHERE f.parent_object_id = s.object_id
) AS s
WHERE OBJECT_NAME (f.referenced_object_id) = 'Languages'结果：
SchemaName, TableName, ColumnName
这是我必须对每个返回行执行的查询，最后求出所有行的总和：
SELECT COUNT(*)
FROM SchemaName.TableName t
WHERE t.ColumnName = @LanguageId我一直在阅读有关游标和动态SQL的文章，但我无法设法使其工作(从未使用过它们)。
游标/动态SQL的使用不是必需的。如果有更简单的方式我会很欣赏。
编辑:我设法让它工作了。EDIT2:对实际需求进行一些重构和全面实现。
DECLARE @WantedDefaultLanguageId INT = 1;
--Internal Variables
DECLARE @DefaultLanguageId INT = (SELECT Id FROM i18n.Languages WHERE IsDefault = 1)
, @SqlCommand NVARCHAR(1000)
, @SchemaName SYSNAME
, @TableName SYSNAME
, @FieldName SYSNAME
, @CurrentValue INT
, @DefaultTotal INT = 0
, @WantedTotal INT = 0;
DECLARE relationships CURSOR
LOCAL FAST_FORWARD READ_ONLY
FOR SELECT schemaNames.SCHEMA_NAME,
OBJECT_NAME(foreignKeys.parent_object_id) AS TableName,
COL_NAME(foreignKeysColumns.parent_object_id, foreignKeysColumns.parent_column_id) AS ColumnName
FROM sys.foreign_keys AS foreignKeys
INNER JOIN sys.foreign_key_columns AS foreignKeysColumns
ON foreignKeys.OBJECT_ID = foreignKeysColumns.constraint_object_id
OUTER APPLY
(
SELECT metadata.SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA metadata
INNER JOIN SYS.TABLES AS sysTables
ON metadata.SCHEMA_NAME = SCHEMA_NAME(sysTables.SCHEMA_ID)
WHERE foreignKeys.parent_object_id = sysTables.object_id
) AS schemaNames
WHERE OBJECT_NAME (foreignKeys.referenced_object_id) = 'Languages';
IF @DefaultLanguageId = @WantedDefaultLanguageId
SELECT 1;
ELSE
BEGIN
OPEN relationships
FETCH NEXT FROM relationships
INTO @SchemaName, @TableName, @FieldName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlCommand = '
SELECT @CurrentValue = COUNT(*)
FROM ' + @SchemaName + '.' + @TableName + ' tableName
WHERE tableName.' + @FieldName + ' = ' + CAST(@DefaultLanguageId AS nvarchar(1000))
EXEC sp_executesql @SqlCommand, N'@CurrentValue INT OUTPUT', @CurrentValue OUTPUT
SET @DefaultTotal += @CurrentValue
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SET @SqlCommand = '
SELECT @CurrentValue = COUNT(*)
FROM ' + @SchemaName + '.' + @TableName + ' tableName
WHERE tableName.' + @FieldName + ' = ' + CAST(@WantedDefaultLanguageId AS nvarchar(1000))
EXEC sp_executesql @SqlCommand, N'@CurrentValue INT OUTPUT', @CurrentValue OUTPUT
SET @WantedTotal += @CurrentValue
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
FETCH NEXT FROM relationships
INTO @SchemaName, @TableName, @FieldName;
END
CLOSE relationships
DEALLOCATE relationships
SELECT CASE
WHEN @WantedTotal = @DefaultTotal THEN
1
ELSE 0
END
END;它需要很长的时间才能运行。这个查询不会经常运行，但任何帮助改进它/更好地实现此功能的方法都是值得感谢的。(稍后我将结束这个问题)。
Edit3:好的，下面是我需要的一个例子：
Table: Language
Id Language
1 English
RelationalTable1
... LanguageId
1
1
2
AllOtherRelationalTables
...我需要计算LanguageId =1的次数(对于引用Language表的所有表)。代码正在工作，但想知道是否有更简单的方法来实现/改进它。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-10-02 16:01:45
帖子中的答案是有效的，可能不是最好的解决方案，但确实有效。
DECLARE @WantedDefaultLanguageId INT = 1;
--Internal Variables
DECLARE @DefaultLanguageId INT = (SELECT Id FROM i18n.Languages WHERE IsDefault = 1)
, @SqlCommand NVARCHAR(1000)
, @SchemaName SYSNAME
, @TableName SYSNAME
, @FieldName SYSNAME
, @CurrentValue INT
, @DefaultTotal INT = 0
, @WantedTotal INT = 0;
DECLARE relationships CURSOR
LOCAL FAST_FORWARD READ_ONLY
FOR SELECT schemaNames.SCHEMA_NAME,
OBJECT_NAME(foreignKeys.parent_object_id) AS TableName,
COL_NAME(foreignKeysColumns.parent_object_id, foreignKeysColumns.parent_column_id) AS ColumnName
FROM sys.foreign_keys AS foreignKeys
INNER JOIN sys.foreign_key_columns AS foreignKeysColumns
ON foreignKeys.OBJECT_ID = foreignKeysColumns.constraint_object_id
OUTER APPLY
(
SELECT metadata.SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA metadata
INNER JOIN SYS.TABLES AS sysTables
ON metadata.SCHEMA_NAME = SCHEMA_NAME(sysTables.SCHEMA_ID)
WHERE foreignKeys.parent_object_id = sysTables.object_id
) AS schemaNames
WHERE OBJECT_NAME (foreignKeys.referenced_object_id) = 'Languages';
IF @DefaultLanguageId = @WantedDefaultLanguageId
SELECT 1;
ELSE
BEGIN
OPEN relationships
FETCH NEXT FROM relationships
INTO @SchemaName, @TableName, @FieldName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SqlCommand = '
SELECT @CurrentValue = COUNT(*)
FROM ' + @SchemaName + '.' + @TableName + ' tableName
WHERE tableName.' + @FieldName + ' = ' + CAST(@DefaultLanguageId AS nvarchar(1000))
EXEC sp_executesql @SqlCommand, N'@CurrentValue INT OUTPUT', @CurrentValue OUTPUT
SET @DefaultTotal += @CurrentValue
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
SET @SqlCommand = '
SELECT @CurrentValue = COUNT(*)
FROM ' + @SchemaName + '.' + @TableName + ' tableName
WHERE tableName.' + @FieldName + ' = ' + CAST(@WantedDefaultLanguageId AS nvarchar(1000))
EXEC sp_executesql @SqlCommand, N'@CurrentValue INT OUTPUT', @CurrentValue OUTPUT
SET @WantedTotal += @CurrentValue
--■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
FETCH NEXT FROM relationships
INTO @SchemaName, @TableName, @FieldName;
END
CLOSE relationships
DEALLOCATE relationships
SELECT CASE
WHEN @WantedTotal = @DefaultTotal THEN
1
ELSE 0
END
END;Stack Overflow用户
发布于 2019-09-28 02:07:41
我不太确定你到底想要什么，但这可能会对你有帮助...
此SQL将为您获取所有FK列信息：
SELECT fkeys.[name] AS FKName,
OBJECT_NAME(fkeys.parent_object_id) AS TableName,
(SELECT STUFF((SELECT ',' + c.[name]
FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
INNER JOIN sys.columns as c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.parent_column_id
AND fc.constraint_object_id = fk.object_id
AND fc.parent_object_id = fk.parent_object_id
WHERE fk.[name] = fkeys.[name]
FOR XML PATH ('')), 1, 1, '')) AS FKFolumns,
OBJECT_NAME(fkeys.referenced_object_id) AS ReferencedTableName,
(SELECT STUFF((SELECT ',' + c.[name]
FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id
INNER JOIN sys.columns as c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.referenced_column_id
AND fc.constraint_object_id = fk.object_id
AND fc.referenced_object_id = fk.referenced_object_id
WHERE fk.[name] = fkeys.[name]
FOR XML PATH ('')), 1, 1, '')) AS ReferencedFKColumns
FROM sys.foreign_keys fkeys您可以使用它来提取引用给定表和列的事物的计数：
WITH AllFKInfo AS (
SELECT fkeys.[name] AS FKName,
OBJECT_NAME(fkeys.parent_object_id) AS TableName,
(SELECT STUFF((SELECT ',' + c.[name]
FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
INNER JOIN sys.columns as c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.parent_column_id
AND fc.constraint_object_id = fk.object_id
AND fc.parent_object_id = fk.parent_object_id
WHERE fk.[name] = fkeys.[name]
FOR XML PATH ('')), 1, 1, '')) AS FKFolumns,
OBJECT_NAME(fkeys.referenced_object_id) AS ReferencedTableName,
(SELECT STUFF((SELECT ',' + c.[name]
FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id
INNER JOIN sys.columns as c ON t.object_id = c.object_id
INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.referenced_column_id
AND fc.constraint_object_id = fk.object_id
AND fc.referenced_object_id = fk.referenced_object_id
WHERE fk.[name] = fkeys.[name]
FOR XML PATH ('')), 1, 1, '')) AS ReferencedFKColumns
FROM sys.foreign_keys fkeys
)
SELECT ReferencedTableName, ReferencedFKColumns, COUNT(ReferencedFKColumns) AS CountOfReferences
FROM AllFKInfo
GROUP BY ReferencedTableName, ReferencedFKColumns
ORDER BY ReferencedTableName, ReferencedFKColumns 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58136730
复制相关文章
相似问题
腾讯云开发者
