你可以创建一个 SQL CLR 表值函数来访问表格。你必须将其绑定到模式,因为 TV-UDF 不支持动态模式。(我的示例包括 ID 和 Title 列 - 根据你的需求进行修改)
完成后,你应该能够执行以下查询:
SELECT * FROM dbo.FromMyTable('table1')
你也可以在该字符串中包含一个多部分名称。
SELECT * FROM dbo.FromMyTable('otherdb..table1')
从该表中返回ID、Title列。
您可能需要启用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()
{
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();
}
}
}