一个查询系统表的sql,请知道的朋友告诉下作用
/* Get the name of the key (or the running code 流水號) and the length of the running code */
DECLARE @kColName nvarchar(100), @kColPrec int , @kColDesc nvarchar(4000), @kColValue nvarchar(4000), @sqlOtherKey nvarchar(4000), @fnXmlParserGetValueByName nvarchar(500)
SET @fnXmlParserGetValueByName = dbo.fnBaseDbName() + '.dbo.fnXmlParserGetValueByName '
SET @sqlOtherKey = ' '
BEGIN
DECLARE FindKeyCursor CURSOR STATIC FORWARD_ONLY
FOR (SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000)) FROM (
syscolumns a INNER JOIN sysobjects b ON a.id = b.id
INNER JOIN sysindexkeys c ON a.colid = c.colid AND b.id = c.id AND c.indid = 1
LEFT OUTER JOIN ::fn_listextendedproperty( 'MS_Description ', 'user ', 'dbo ', 'TABLE ', ' ', 'column ', DEFAULT) d ON a.name = d.objname COLLATE database_default
) WHERE b.type = 'U '
UNION SELECT a.name, a.prec, CAST(d.value AS nvarchar(4000)) FROM (
syscolumns a INNER JOIN sysobjects b ON a.id = b.id
LEFT OUTER JOIN ::fn_listextendedproperty( 'MS_Description ', 'user ', 'dbo ', 'VIEW ', ' ', 'column ', DEFAULT) d ON a.name = d.objname COLLATE database_default
) WHERE b.type = 'V ' )
OPEN FindKeyCursor
FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @kColName
IF CHARINDEX( ' <docno> ',@kColDesc) > 0 OR @@CURSOR_ROWS = 1-- has <docno> or no of row is exactly one
BEGIN
SET @KeyName = @kColName
SET @Length = @kColPrec
END
ELSE-- does not have <docno> , so it is OtherKey
BEGIN
EXEC @kColValue = @fnXmlParserGetValueByName @OtherKey, @kColName
SET @sqlOtherKey = @sqlOtherKey + ' AND ' +@kColName + ' = ' ' '+ IsNull(@kColValue, ' ') + ' ' ' '
END
FETCH NEXT FROM FindKeyCursor INTO @kColName, @kColPrec , @kColDesc
END
CLOSE FindKeyCursor
DEALLOCATE FindKeyCursor
END
里面的fnXmlParserGetValueByName 函数为
http://community.csdn.net/Expert/topic/5323/5323665.xml?temp=.4541132
的内容
[解决办法]
fnBaseDbName()是什么
[解决办法]
Get the name of the key (or the running code 流水號) and the length of the running code
这句话说的很明确了啊,就是得到流水号的键名和长度
[解决办法]
呵呵,有注释的嘛 :)
