SP_CreateModel，sql server 自动生成表的实体类的通用存储过程

--调用该sp, 执行语句即可

--EXEC dbo.SP_CreateModel 't_Buss_LargeScreenTotalInfoByMonth'

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER PROCEDURE SP_CreateModel(
@TableName sysname
)
AS
BEGIN
--declare @TableName sysname = 'T1'
declare @Result varchar(max) = '
/// <summary>
/// ' + @TableName +

'
/// </summary>
public class ' + @TableName + '
{'

select @Result = @Result + '
/// <summary>
/// ' + CONVERT(NVARCHAR(500), ISNULL(ColName, '无')) +

'
/// </summary>
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
SELECT
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
prop.value ColName,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'bool'
when 'char' then 'string'
when 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'datetimeoffset' then 'DateTimeOffset'
when 'decimal' then 'decimal'
when 'float' then 'float'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'char'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'double'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'DateTime'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
LEFT JOIN sys.extended_properties prop ON col.object_id = prop.major_id AND col.column_id = prop.minor_id
where object_id = object_id(@TableName)
) t
--order by ColumnId