日期:2014-05-18  浏览次数:20514 次

一个查询系统表的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

里面的fnXmlParserGetVa