常用的SQL语句

浏览数：26 / 时间：2015年06月12日

1.查询数据表名称、字段、字段说明

SELECT 
t.[name] AS 表名,
c.[name] AS 字段名,
cast(ep.[value] as nvarchar(200)) AS [字段说明]  
FROM sys.tables AS t  
INNER JOIN sys.columns   
AS c ON t.object_id = c.object_id  
 LEFT JOIN sys.extended_properties AS ep   
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1   
AND t.name='TableName'

2. SQL自定义函数和递归with（注意：with递归默认层级是：100）

ALTER function [dbo].[fun_GetMemNewsGroupParentIDs](@NewGrpIDs nvarchar(500))
returns @t table(NewGrpID int,ParentID int)
as
begin
WITH T AS  
(  
SELECT NewGrpID,ParentID FROM MemNewsGroup  
WHERE charindex(','+ltrim(NewGrpID)+',',','+@NewGrpIDs+',') > 0
UNION ALL  
SELECT A.NewGrpID,A.ParentID 
FROM MemNewsGroup AS A JOIN T AS B ON A.NewGrpID = B.ParentID
)
insert into @t select distinct NewGrpID,ParentID from T
RETURN
end

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

常用的SQL语句

标签： sql2008sql2008