sp_MSForEachDB在函数内无效使用副作用运算符 [英] sp_MSForEachDB Invalid Use of Side-Effecting Operator Within Function

问题描述

无论如何，都可以将以下代码放入一个表值(内联或多语句)或一个View中.我可以在存储过程中使用它，但是由于无法连接存储过程的结果，所以我希望将其放在表值函数或视图中.

Is there anyway to put the below code into a Table-Valued (inline or multi-statement) or even a View. I'm able to use it in a stored procedure but do to the inability to do joins to the results of stored procedures I'd rather put it in a Table-Valued function or View.

下面是为我提供所需结果的SQL

Below is the SQL that gives me the results I'm looking for

DECLARE @ColumnInformation TABLE
(
    DatabaseName NVARCHAR(255),
    TableSchema NVARCHAR(255),
    TableName NVARCHAR(255),
    ColumnName NVARCHAR(255),
    TableType NVARCHAR(255),
    FullyQualifiedTableName NVARCHAR(255),
    FullyQualifiedColumnName NVARCHAR(255)
)
INSERT INTO @ColumnInformation
EXECUTE master.sys.sp_MSForEachDB  '
IF ''?'' NOT IN (''master'', ''tempdb'', ''msdb'', ''model'', ''ReportServer'', ''ReportServerTempDB'' )
BEGIN
    USE [?];
    PRINT ''?''
    SELECT 
         T.TABLE_CATALOG AS DatabaseName
        ,T.TABLE_SCHEMA as TableSchema  
        ,T.TABLE_NAME AS TableName
        ,C.COLUMN_NAME AS ColumnName
        ,T.TABLE_TYPE AS TableType
        ,''['' + T.TABLE_CATALOG + ''].['' + T.TABLE_SCHEMA + ''].['' + T.TABLE_NAME + '']'' AS FullyQualifiedTableName
        ,''['' + T.TABLE_CATALOG + ''].['' + T.TABLE_SCHEMA + ''].['' + T.TABLE_NAME + ''].['' + C.COLUMN_NAME + '']'' FullyQualifiedColumnName
    FROM INFORMATION_SCHEMA.TABLES as t inner join
        INFORMATION_SCHEMA.COLUMNS as c on c.table_name = t.table_name
END
'

SELECT * 
FROM @ColumnInformation
ORDER BY DatabaseName, TableSchema, TableName, ColumnName

但是，当我尝试将其放入表值函数中时，却出现了错误

however when I try to put it in a table valued function I'm getting the error

Msg 443, Level 16, State 14, Procedure fGetAllColumnInformation, Line 17
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

我相信sp_MSForEachDb正在生成动态SQL，但不是真正的确定.我隐约记得曾经听说过动态SQL不能在表值函数中使用.如果是这种情况，无论如何都可以绕过该限制，以便我可以在表值函数或视图中使用以上代码.

I believe that sp_MSForEachDb is generating dynamic SQL but not real sure. I vaguely remember hearing that dynamic SQL can not be used in table-valued functions. If this is the case is there anyway of bypassing that restriction so that I can use the above code in a table-valued function or view.

如果上述答案是否定的...是否有任何方法可以重写该语句以使其不使用动态SQL?

If the answer to the above is no...is there any way of rewriting the statement so that it does not use dynamic SQL?

我基本上是在尝试合并SQL Server上每个数据库的所有INFORMATION_SCHEMA.COLUMNS的结果.

I'm basically trying to union the results of all of the INFORMATION_SCHEMA.COLUMNS from every database on a SQL Server.

推荐答案

如果没有动态SQL，就无法动态执行此操作(如不预先对所有数据库名称进行硬编码)，也无法使用动态SQL.在功能上.您在任何情况下都不应该使用sp_MSForEachDB 或恕我直言，

You can't do this dynamically (as in without hard-coding all the database names in advance) without dynamic SQL, and you can't use dynamic SQL in a function. You shouldn't be using sp_MSForEachDB in any case or, IMHO, using INFORMATION_SCHEMA.

编写一个存储过程以返回结果集.如果您绝对需要将输出与其他内容连接(为什么不将其编码到存储过程中?)，则将输出插入#temp表中.

Write a stored procedure that returns the result set. If you absolutely need to join the output to other stuff (why not code that into the stored procedure?), then insert the output into a #temp table.

尝试一下:

CREATE PROCEDURE dbo.AllMyColumnsEverywhereForReals
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'';

  SELECT @sql += '
  UNION ALL SELECT 
      [database]  = N''' + d.name + ''' COLLATE SQL_Latin1_General_CP1_CI_AI,
      [schema]    = s.name COLLATE SQL_Latin1_General_CP1_CI_AI,
      [object]    = o.name COLLATE SQL_Latin1_General_CP1_CI_AI,
      [column]    = c.name COLLATE SQL_Latin1_General_CP1_CI_AI,
      [qualified] = QUOTENAME(''' + d.name + ''') 
        + ''.'' + QUOTENAME(s.name) 
        + ''.'' + QUOTENAME(c.name) COLLATE SQL_Latin1_General_CP1_CI_AS,
      [type] = CASE o.type WHEN ''U'' THEN ''Table'' ELSE ''View'' END
    FROM ' + QUOTENAME(d.name) + '.sys.columns AS c
      INNER JOIN ' + QUOTENAME(d.name) + '.sys.objects AS o
      ON c.[object_id] = o.[object_id]
      INNER JOIN ' + QUOTENAME(d.name) + '.sys.schemas AS s
      ON o.[schema_id] = s.[schema_id]
      WHERE o.type IN (''U'', ''V'')'
  FROM sys.databases AS d WHERE [state] = 0 AND name NOT IN 
    (N'master',N'tempdb',N'msdb',N'model',N'ReportServer',N'ReportServerTempDB');

  SET @sql = STUFF(@sql, 1, 13, '');

  EXEC sp_executesql @sql;
END
GO

用法:

CREATE TABLE #x
(
  db     SYSNAME, 
  sch    SYSNAME, 
  obj    SYSNAME, 
  col    SYSNAME, 
  qual   NVARCHAR(390),
  [type] CHAR(5)
);

INSERT #x EXEC dbo.AllMyColumnsEverywhereForReals;

SELECT cols FROM #x AS x -- INNER JOIN something else ON x.whatever...

这篇关于sp_MSForEachDB在函数内无效使用副作用运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！