获取表结构表值函数 Fun_GetTableStru

 

IF OBJECT_ID('[dbo].[Fun_GetCleanColDesc]') IS NOT NULL
	DROP FUNCTION [dbo].[Fun_GetCleanColDesc]
GO
-- =============================================
-- Author:		yennage
-- Create date: 2018-07-17
-- Description:	将描述从分隔符处分断开，不取后面的串
-- =============================================
CREATE FUNCTION [dbo].[Fun_GetCleanColDesc]
(
	@str NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @r NVARCHAR(MAX),@s NCHAR(1)
	IF @str IS NULL
		RETURN NULL;
	IF @str=''
		RETURN ''; 
	
	SET @str=LTRIM(RTRIM(@str))
	
	SET @r=''
	WHILE LEN(@str)>0
	BEGIN
		SET @s=LEFT(@str,1);
		SET @str=SUBSTRING(@str,2,LEN(@str)-1);
		
		IF @s IN(' ','：',':','。','.','，')
		BEGIN
			BREAK;
		END
		
		SET @r= @r + @s;
	END
 
	RETURN @r
END
GO
IF OBJECT_ID('[dbo].[Fun_GetTableStru]') IS NOT NULL
	DROP FUNCTION [dbo].[Fun_GetTableStru] 
GO
-- =============================================
-- Author:	yenange
-- Create date: 2018-07-24
-- Description:	获取表结构
-- =============================================
CREATE FUNCTION [dbo].[Fun_GetTableStru] 
(	
	@tableName NVARCHAR(MAX)
)
RETURNS TABLE 
AS
RETURN 
(
SELECT c.column_id            AS columnId,
       c.name                 AS columnName,
       tp.name				  AS dataType,
       CASE WHEN tp.name IN ('nchar','nvarchar')  AND c.max_length>0  THEN c.max_length/2 ELSE c.max_length END AS [maxLength],
       c.is_nullable AS isNullable,
       CASE WHEN EXISTS(
       		SELECT * FROM sys.indexes AS i 
			INNER JOIN sys.index_columns AS ic ON i.[object_id]=ic.[object_id] AND i.index_id=ic.index_id
		    WHERE i.is_primary_key=1 AND i.[object_id]=t.[object_id] AND ic.column_id=c.column_id
       ) THEN 1 ELSE 0 END AS isPK,
       CASE WHEN EXISTS(
			SELECT 1 FROM sys.foreign_key_columns AS fkc WHERE fkc.parent_object_id=c.[object_id] AND fkc.constraint_column_id=c.column_id
       ) THEN 1 ELSE 0 END AS isFK,
       c.is_identity AS isIdentity,
       c.is_computed AS isComputed,
       (SELECT cmp.definition
		 FROM sys.computed_columns cmp with(nolock) WHERE cmp.[object_id]=c.[object_id] AND c.column_id=cmp.column_id) AS [calcuExpress]
	   ,(select top 1 
			CASE 
	            WHEN s.[text] IS NULL THEN ''
	            WHEN s.[text] = 'getdate()' OR s.[text] ='(getdate())' THEN 'getdate()'
	            ELSE REPLACE(REPLACE(s.[text], '(', ''), ')', '')
	       END FROM sys.syscomments AS s WHERE s.id=c.default_object_id) AS defaultValue
       ,[dbo].[Fun_GetCleanColDesc](CONVERT(NVARCHAR(MAX), ep.value)) AS columnDesc
       ,ep.[value] AS [columnDetailDesc]
       ,ep2.[value] AS [tableDesc]
FROM   sys.[columns]          AS c
       INNER JOIN sys.tables  AS t
            ON  c.[object_id] = t.[object_id]
            AND t.[object_id] = OBJECT_ID(@tableName)
       INNER JOIN sys.types   AS tp
            ON  c.system_type_id = tp.system_type_id
            AND c.user_type_id = tp.user_type_id
	   LEFT JOIN sys.extended_properties AS ep  ON ep.major_id=t.[object_id] AND ep.minor_id=c.column_id AND ep.name='MS_Description'
	   LEFT JOIN sys.extended_properties AS ep2  ON ep2.major_id=t.[object_id] AND ep2.minor_id=0 AND ep2.name='MS_Description'
)
GO