SQL Server--------SQL Server常用(查看注释,新增注释,生成实体,查看表结构信息,case when...)
--新增字段:
--修改字段类型:
ALTER TABLE line_info
ALTER COLUMN line_remark INT
--CASE WHEN 遇到NULL时的处理方法
☞:CASE result WHEN NULL THEN '否'ELSE '是' END result1,结果永远是'是'。(错误 ✘)
☞:CASE WHEN result IS NULL THEN '否' ELSE '是' END result2 ,结果正确。 (正确 ✔)
SELECT
col.name AS 名称 ,
typ.name AS 类型 ,
CASE WHEN col.is_nullable = 0 THEN '否' ELSE '是' END AS 是否为空 ,
col.max_length AS 长度 ,
ISNULL(def.definition, '') AS 默认值,
extprop.value AS 描述
FROM sys.tables tab
JOIN sys.columns (NOLOCK) col ON tab.object_id = col.object_id
JOIN sys.extended_properties (NOLOCK) extprop ON col.object_id = extprop.major_id AND col.column_id=extprop.minor_id
JOIN sys.types (NOLOCK) typ ON col.user_type_id = typ.user_type_id
LEFT JOIN sys.default_constraints (NOLOCK) def ON col.object_id = def.parent_object_id
AND col.column_id = def.parent_column_id
LEFT JOIN sys.index_columns (NOLOCK) indcol ON indcol.object_id = col.object_id
AND indcol.column_id = col.column_id
LEFT JOIN sys.indexes (NOLOCK) ind ON ind.index_id = indcol.index_id
AND ind.object_id = indcol.object_id
AND is_primary_key = 1
WHERE extprop.name = 'MS_Description'
AND col.column_id = extprop.minor_id
AND tab.name IN ( 'payment_details' )
ORDER BY tab.name ,col.column_id
--根据表结构生成实体
Declare @tableName nvarchar(300)
Declare @content nvarchar(max)
declare @value nvarchar(max)
set @content = ''
set @tableName = 'payment_details'
set @value = (
select --d.value 说明,
'
/// <summary>'+'
/// Description:'+isnull(cast( d.value As varchar(200)) ,'')+
'
/// </summary>'+'
public '
+ case c.name when 'uniqueidentifier' then 'Guid'
when 'int' then 'int'
when 'uniqueidentifier' then 'Guid'
when 'datetime' then 'DateTime'
when 'decimal' then 'double'
Else 'string' End +' '+b.name+ ' { get; set; }'
--a.name 表名,b.name 字段名,c.name 字段类型,c.length 字段长度 ,b.*
from sysobjects a left join syscolumns b on a.id=b.id left join systypes c on b.xtype=c.xtype
left join sys.extended_properties d on d.major_id = a.id and b.colorder = minor_id
where 1=1
and a.name=@tableName and a.xtype='U'
and c.name <> 'sysname'
for xml path('')
)
set @value = replace(@value,'<','<')
set @value = replace(@value,'>','>')
set @value= replace( @value,'
','')
--print @columns
set @content = 'using JnsFramework.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Eto
{
public class '+replace(@tableName,'_','')+'Entity : IModel
{
'+
@value
+'
}
}'
select cast(@content as xml)
✔