实现的效果，如：找出数据库表索引：表名，索引号，列名（逗号分隔）
select i.object_id as tab_id , i.index_id , i.index_column_id , c.name into #t from sys.index_columns as i join sys.all_columns as c on ( i.object_id = c.object_id and i.column_id = c.column_id) where i.object_id in ( select object_id from sys.tables ) order by object_name(i.object_id) asc,i.index_id asc ,i.index_column_id asc select object_name(tab_id) , index_id , dbo.JoinStr( /*cast(index_column_id as varchar(10)) + */ name ,',') as col_name from #t group by object_name(tab_id), index_id
帮助里的例子：
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_2devguide/html/5a188b50-7170-4069-acad-5de5c915f65d.htm
我的实现：
using System; using System.IO; using System.Collections; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlTypes; using System.Data.Sql; using System.Text.RegularExpressions; using Microsoft.SqlServer.Server; //ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_2devguide/html/5a188b50-7170-4069-acad-5de5c915f65d.htm /// <summary> /// This class is provides regular expression operations for Transact-SQL callers /// </summary> public sealed class RegularExpression { private RegularExpression() { } /// <summary> /// This method returns a table of matches, groups, and captures based on the input /// string and pattern string provided. /// </summary> /// <param name="sqlInput">What to match against</param> /// <param name="sqlPattern">What to look for</param> /// <returns>An object which appears to be reading from SQL Server but which in fact is reading /// from a memory based representation of the data.</returns> [SqlFunction(FillRowMethodName = "FillRow")] public static IEnumerable Matches(SqlString sqlInput, SqlString sqlPattern) { string input = (sqlInput.IsNull) ? string.Empty : sqlInput.Value; string pattern = (sqlPattern.IsNull) ? string.Empty : sqlPattern.Value; return GetMatches(input, pattern); } public static void FillRow(object obj, out int matchId, out int matchIndex, out string matchValue, out int groupId, out int groupIndex, out string groupValue, out int captureIndex, out string captureValue) { MatchResult result = (MatchResult)obj; matchId = result.MatchID; matchIndex = result.MatchIndex; matchValue = result.MatchValue; groupId = result.GroupID; groupIndex = result.GroupIndex; groupValue = result.GroupValue; captureIndex = result.CaptureIndex; captureValue = result.CaptureValue; } /// <summary> /// Generates a list of Match/Group/Capture tuples represented using the /// MatchResult struct based on the regular expression match of the input /// string and pattern string provided. /// </summary> /// <param name="input">What to match</param> /// <param name="pattern">What to look for</param> /// <returns>A list of Match/Group/Capture tuples</returns> private static List<MatchResult> GetMatches(string input, string pattern) { List<MatchResult> result = new List<MatchResult>(); int matchId = 0; int groupId = 0; foreach (Match m in Regex.Matches(input, pattern)) { if (m.Groups.Count < 1) result.Add(new MatchResult(matchId, m.Index, m.Value, -1, -1, string.Empty, -1, string.Empty)); else { groupId = 0; foreach (Group g in m.Groups) { if (g.Captures.Count < 1) result.Add(new MatchResult(matchId, m.Index, m.Value, groupId, g.Index, g.Value, -1, string.Empty)); else { foreach (Capture c in m.Groups) { result.Add(new MatchResult(matchId, m.Index, m.Value, groupId, g.Index, g.Value, c.Index, c.Value)); } } groupId += 1; } } matchId += 1; } return result; } /// <summary> /// This method performs a pattern based substitution based on the provided input string, pattern /// string, and replacement string. /// </summary> /// <param name="sqlInput">The source material</param> /// <param name="sqlPattern">How to parse the source material</param> /// <param name="sqlReplacement">What the output should look like</param> /// <returns></returns> public static string Replace(SqlString sqlInput, SqlString sqlPattern, SqlString sqlReplacement) { string input = (sqlInput.IsNull) ? string.Empty : sqlInput.Value; string pattern = (sqlPattern.IsNull) ? string.Empty : sqlPattern.Value; string replacement = (sqlReplacement.IsNull) ? string.Empty : sqlReplacement.Value; return Regex.Replace(input, pattern, replacement); } } /// <summary> /// This struct is used trepresents a Match/Group/Capture tuple. Instances of this struct are /// created by the GetMatches method. /// </summary> internal struct MatchResult { /// <summary> /// Which match this is /// </summary> private int _matchID; public int MatchID { get { return this._matchID; } } /// <summary> /// Where the match starts in the input string /// </summary> private int _matchIndex; public int MatchIndex { get { return this._matchIndex; } } /// <summary> /// What string matched the pattern /// </summary> private string _matchValue; public string MatchValue { get { return this._matchValue; } } /// <summary> /// Which matching group this is /// </summary> private int _groupID; public int GroupID { get { return this._groupID; } } /// <summary> /// Where this group starts in the input string /// </summary> private int _groupIndex; public int GroupIndex { get { return this._groupIndex; } } /// <summary> /// What the group matched in the input string /// </summary> private string _groupValue; public string GroupValue { get { return this._groupValue; } } /// <summary> /// Where this capture starts in the input string /// </summary> private int _captureIndex; public int CaptureIndex { get { return this._captureIndex; } } /// <summary> /// What the capture matched in the input string /// </summary> private string _captureValue; public string CaptureValue { get { return this._captureValue; } } /// <summary> /// A convenient constructor which fills in all the fields contained in this struct. /// </summary> /// <param name="matchID">Which match this is</param> /// <param name="matchIndex">Where the match starts in the input string</param> /// <param name="matchValue">What string matched the pattern</param> /// <param name="groupID">Which matching group this is</param> /// <param name="groupIndex">Where this group starts in the input string</param> /// <param name="groupValue">What the group matched in the input string</param> /// <param name="captureIndex">Where this capture starts in the input string</param> /// <param name="captureValue">What the capture matched in the input string</param> public MatchResult(int matchId, int matchIndex, string matchValue, int groupId, int groupIndex, string groupValue, int captureIndex, string captureValue) { this._matchID = matchId; this._matchIndex = matchIndex; this._matchValue = matchValue; this._groupID = groupId; this._groupIndex = groupIndex; this._groupValue = groupValue; this._captureIndex = captureIndex; this._captureValue = captureValue; } } public sealed class StringSplitter { /// <summary> /// The streaming table-valued function used to split the string into a relation /// </summary> /// <param name="argument"></param> /// <returns></returns> [SqlFunction(FillRowMethodName = "FillRow")] public static IEnumerable Split(SqlString argument) { string value; if (argument.IsNull) value = ""; else value = argument.Value; return value.Split(','); } public static void FillRow(Object obj, out string stringElement) { stringElement = (string)obj; } /// <summary> /// Don't allow callers to create instances of this class /// </summary> private StringSplitter() { } } [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate( Microsoft.SqlServer.Server.Format.UserDefined, //use clr serialization to serialize the intermediate result IsInvariantToNulls = true,//optimizer property IsInvariantToDuplicates = false,//optimizer property IsInvariantToOrder = false,//optimizer property MaxByteSize = 8000)//maximum size in bytes of persisted value ] public class JoinStr : Microsoft.SqlServer.Server.IBinarySerialize { /// <summary> /// The variable that holds the intermediate result of the concatenation /// </summary> private StringBuilder intermediateResult; private string joinString; /// <summary> /// Initialize the internal data structures /// </summary> public void Init() { intermediateResult = new StringBuilder(); } /// <summary> /// Accumulate the next value, nop if the value is null /// </summary> /// <param name="value"></param> public void Accumulate( SqlString value,SqlString joinString) { this.joinString = joinString.IsNull ? "," : joinString.Value; if (value.IsNull) { return; } intermediateResult.Append(value.Value).Append(this.joinString); } /// <summary> /// Merge the partially computed aggregate with this aggregate. /// </summary> /// <param name="other"></param> public void Merge(JoinStr other) { intermediateResult.Append(other.intermediateResult); } /// <summary> /// Called at the end of aggregation, to return the results of the aggregation /// </summary> /// <returns></returns> public SqlString Terminate() { string output = string.Empty; //delete the trailing comma, if any if (intermediateResult != null && intermediateResult.Length > 0) output = intermediateResult.ToString(0, intermediateResult.Length - (this.joinString == null ? 1 : this.joinString.Length)); return new SqlString(output); } public void Read(BinaryReader r) { if (r == null) throw new ArgumentNullException("r"); intermediateResult = new StringBuilder(r.ReadString()); } public void Write(BinaryWriter w) { if (w == null) throw new ArgumentNullException("w"); w.Write(intermediateResult.ToString()); } }
安装：
exec sp_configure 'show advanced options', '1'; go reconfigure; go exec sp_configure 'clr enabled', '1' go reconfigure; exec sp_configure 'show advanced options', '1'; go CREATE ASSEMBLY MyCLr FROM 'G:\共享\个人共享\Udi\MyClr\MyClr.dll' WITH permission_set = Safe; GO CREATE AGGREGATE [dbo].JoinStr(@input nvarchar(4000) , @sep nvarchar(5) ) RETURNS nvarchar(4000) EXTERNAL NAME MyCLr.JoinStr; go
