在 T-SQL 中评估

Question

我有一个存储过程，允许 IN 参数指定要使用的数据库。 然后，我使用该数据库中预先确定的表进行查询。 我遇到的问题是将表名称连接到查询中的数据库名称。 如果 T-SQL 有一个评估函数，我可以执行类似“

eval(@dbname + 'MyTable')

当前我一直在创建一个字符串，然后使用 exec() 将该字符串作为查询运行”之类的操作。 这很混乱，我宁愿不必创建字符串。 有没有办法可以评估变量或字符串，以便我可以执行如下操作？

SELECT *
FROM eval(@dbname + 'MyTable')

我希望它进行评估，所以它最终会出现这样的情况：

SELECT *
FROM myserver.mydatabase.dbo.MyTable

Answer 1

没有“更简洁”的方法可以做到这一点。 如果您接受它并查看其他内容，则会节省时间。

编辑：啊哈！ 关于OP的评论“我们必须每月将数据加载到新数据库中，否则它会变得太大。”。 回想起来令人惊讶的是，没有人评论这个问题的微弱气味。

SQL Server 提供了处理“太大”表（特别是分区）的本机机制，这将允许您将表作为单个实体进行处理，同时在后台将表划分为单独的文件，从而消除当前的情况。完全有问题。

换句话说，这是数据库管理员的问题，而不是数据库消费者的问题。 如果您也遇到这种情况，我建议您研究分区 这个表。

Answer 2

尝试 sp_executesql 内置函数。
你基本上可以在你的过程中构建你的SQL字符串，然后调用

exec sp_executesql @SQLString.

DECLARE @SQLString nvarchar(max)
SELECT @SQLString = '
SELECT *
FROM  ' +  @TableName 

EXEC sp_executesql @SQLString

Answer 3

您无法在 SQL Server 中指定动态表名称。

有几个选项：

1. 使用动态 SQL
2. 使用同义词（这意味着动态 SQL 较少，但仍然有一些）

您已经说过您不喜欢 1，所以让我们选择 2。

第一个选项是将混乱限制为一个line：

begin transaction t1;
declare @statement nvarchar(100);

set @statement = 'create synonym temptablesyn for db1.dbo.test;'
exec sp_executesql @statement

select * from db_syn

drop synonym db_syn;

rollback transaction t1;

我不确定我喜欢这个，但这可能是你最好的选择。 这样所有的 SELECT 都将是相同的。

您可以将其重构为您喜欢的内容，但是这样做有很多缺点，包括同义词是在事务中创建的，因此您不能有两个
同时运行的查询数
时间（因为双方都会尝试
创建 temptablesyn）。 取决于
根据锁定策略，我们将
阻止对方。

同义词是永久性的，因此这就是您需要在事务中执行此操作的原因。

Answer 4

有一些选择，但它们比您已经做的方式更混乱。 我建议您：
(1) 坚持目前的做法
(2) 继续将 SQL 嵌入代码中，因为无论如何您都会这样做。
(3) 要格外小心地验证您的输入以避免 SQL 注入。

而且，混乱并不是动态 SQL 的唯一问题。 请记住以下几点：
(1) 动态 SQL 阻碍服务器创建可重用执行计划的能力。
(2) ExecuteSQL 命令破坏了所有权链。 这意味着代码将在调用存储过程的用户而不是过程的所有者的上下文中运行。 这可能会迫使您在运行该语句的任何表上打开安全性，并产生其他安全问题。

Answer 5

只是一个想法，但如果您有这些数据库的预定义列表，那么您可以在连接到的数据库中创建一个视图来加入它们 - 类似于：

CREATE VIEW dbo.all_tables
AS

SELECT  your_columns,
        'db_name1' AS database_name
FROM    db_name1.dbo.your_table

UNION ALL

SELECT  your_columns,
        'db_name2'
FROM    db_name2.dbo.your_table

etc...

然后，您可以将数据库名称传递到存储的数据库中过程并简单地将其用作 WHERE 子句中的参数。 如果表很大，您可能会考虑使用索引视图，在新的database_name列（或您所说的任何名称）和表的主键上建立索引（我从问题中假设表的架构是相同的？ ）。

显然，如果您的数据库列表经常更改，那么这会变得更加成问题 - 但如果您无论如何都必须创建这些数据库，那么同时维护此视图应该不会有太大的开销！

Answer 6

我认为马克·布里廷汉姆的想法是正确的（此处：
http://stackoverflow.com/questions /688425/evaluate-in-t-sql/718223#718223)，即发出 use database 命令并写入 sp 以不完全限定表名。 正如他所指出的，这将作用于登录名当前数据库中的表。

让我添加一些可能的阐述：

根据OP的评论，我收集数据库每月更改一次，当它变得“太大”时。 （“我们必须每个月将数据加载到新数据库中，否则它会变得太大。– d03boy”）

1. 用户登录有一个默认数据库，使用 sp_defaultdb（已弃用）或 ALTER LOGIN 设置。 如果每个月您都会转移到新数据库，并且不需要在旧副本上运行 sp，只需每月更改登录的默认数据库，并且再次强调，不要完全限定表名。

2. 可以在客户端登录中设置要使用的数据库：
   sqlcmd -U login_id -P password -d db_name，然后从那里执行 sp。

3. 您可以使用您选择的客户端（命令行、ODBC、JDBC）建立与数据库的连接，然后发出 use database 命令，执行 sp。

   使用数据库栏；
   exec sp_foo;

使用上述方法之一设置数据库后，您可以选择执行存储过程的三种选择：

1. 您可以将 sp 与数据库一起复制到新数据库中。 只要表名不是完全限定的，您就可以对新数据库的表进行操作。

   exec sp_foo;

2. 您可以在其自己的数据库中安装 sp 的单个规范副本，将其称为 procs，表名不完全限定，然后调用其完全限定名称：

   exec procs.dbo.sp_foo;

3. 您可以在每个单独的数据库中安装一个存根 sp_foo 来执行真实 sp 的完全限定名称，然后执行 sp_foo 而不对其进行限定。 存根将被调用，并且它将调用 procs 中的真实过程。 （不幸的是，use database dbname 无法在 sp 内执行。）

   --sp_foo 存根： 
     创建过程 bar.dbo.sp_foo  
      @parm int 
     作为 
     开始 
       执行 procs.dbo.sp_foo @parm; 
     结尾 
     go

但是，这样做后，如果数据库正在更改，则应使用 WITH RECOMPILE 选项，否则它将缓存错误表的执行计划。 存根当然不需要这个。

Answer 7

您可以创建 SQL CLR 表值 UDF 来访问表。 您必须将其与架构联系起来，因为 TV-UDF 不支持动态架构。 （我的示例包括 ID 和标题列 - 根据您的需要进行修改）

完成此操作后，您应该能够执行以下查询：

SELECT * FROM dbo.FromMyTable('table1')

您也可以在该字符串中包含多部分名称。

SELECT * FROM dbo.FromMyTable('otherdb..table1')

返回该表中的 ID、标题列。

您可能需要启用 SQL CLR 并打开 TRUSTWORTHY 选项：

sp_configure 'clr enabled',1
go
reconfigure
go
alter database mydatabase set trustworthy on

创建一个 C# SQL 项目，添加一个新的 UDF 文件，然后将其粘贴到其中。 将项目属性、数据库、权限级别设置为外部。 构建、部署。 无需 VisualStudio 即可完成。 如果您需要，请告诉我。

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlClient;

[assembly: CLSCompliant(true)]
namespace FromMyTable
{
    public static partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, SystemDataAccess = SystemDataAccessKind.Read, IsPrecise = true, FillRowMethodName = "FillRow", 
            TableDefinition = "id int, title nvarchar(1024)")]
        public static IEnumerable FromMyTable(SqlString tableName)
        {
            return new FromMyTable(tableName.Value);
        }

        public static void FillRow(object row, out SqlInt32 id, out SqlString title)
        {
            MyTableSchema v = (MyTableSchema)row;
            id = new SqlInt32(v.id);
            title = new SqlString(v.title);
        }
    }

    public class MyTableSchema
    {
        public int id;
        public string title;
        public MyTableSchema(int id, string title) { this.id = id; this.title = title; }
    }

    internal class FromMyTable : IEnumerable
    {
        string tableName;

        public FromMyTable(string tableName)
        {
            this.tableName = tableName;
        }

        public IEnumerator GetEnumerator()
        {
            return new FromMyTableEnum(tableName);
        }
    }

    internal class FromMyTableEnum : IEnumerator
    {
        SqlConnection cn;
        SqlCommand cmd;
        SqlDataReader rdr;
        string tableName;

        public FromMyTableEnum(string tableName)
        {
            this.tableName = tableName;
            Reset();
        }

        public MyTableSchema Current
        {
            get { return new MyTableSchema((int)rdr["id"], (string)rdr["title"]); }
        }

        object IEnumerator.Current
        {
            get { return Current; }
        }

        public bool MoveNext()
        {
            bool b = rdr.Read();
            if (!b) { rdr.Dispose(); cmd.Dispose(); cn.Dispose(); rdr = null; cmd = null; cn = null; }
            return b;
        }

        public void Reset()
        {
            // note: cannot use a context connection here because it will be closed
            // in between calls to the enumerator.
            if (cn == null) { cn = new SqlConnection("server=localhost;database=mydatabase;Integrated Security=true;"); cn.Open(); }
            if (cmd == null) cmd = new SqlCommand("select id, title FROM " + tableName, cn);
            if (rdr != null) rdr.Dispose();
            rdr = cmd.ExecuteReader();
        }
    }
}

Answer 8

declare @sql varchar(256);
set @sql = 'select * into ##myGlobalTemporaryTable from '+@dbname
exec sp_executesql @sql

select * from ##myGlobalTemporaryTable

复制到全局临时表中，然后您可以像常规表一样使用它

Answer 9

如果您有相当数量的数据库，最好使用预定义的条件语句，例如：

if (@dbname = 'db1')
  select * from db1..MyTable
if (@dbname = 'db2')
  select * from db2..MyTable
if (@dbname = 'db3')
  select * from db3..MyTable

...

如果您要更改可查询的数据库列表，您可以生成此过程作为数据库创建脚本的一部分。

这避免了动态 SQL 的安全问题。 您还可以通过将“select”语句替换为针对每个数据库的存储过程（每个查询 1 个缓存的执行计划）来​​提高性能。

Answer 10

if exists (select * from master..sysservers where srvname = 'fromdb')
    exec sp_dropserver 'fromdb'
go

declare @mydb nvarchar(99);
set @mydb='mydatabase'; -- variable to select database

exec sp_addlinkedserver @server = N'fromdb',
    @srvproduct = N'',
    @provider = N'SQLOLEDB', 
    @datasrc = @@servername,
    @catalog = @mydb
go

select * from OPENQUERY(fromdb, 'select * from table1') 
go 

Answer 11

阅读此...动态 SQL 的诅咒和祝福，帮助我了解如何解决这类问题。