常用的SQL語句，常用SQL語句

常用的SQL語句，常用SQL語句

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)asbeginWITH T AS  (  SELECT NewGrpID,ParentID FROM MemNewsGroup  WHERE charindex(','+ltrim(NewGrpID)+',',','+@NewGrpIDs+',') > 0UNION 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 TRETURNend