SQL Server--------SQL Server常用（查看注释，新增注释，生成实体，查看表结构信息，case when...）

--新增字段：

ALTER TABLE line_info

ADD line_remark NVARCHAR(MAX) DEFAULT '' 

EXECUTE sp_addextendedproperty  'MS_Description', '线路备注', 'user', 'dbo', 'table', 'line_info', 'column', 'line_remark'; --字段释义

--修改字段类型：

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  ，结果正确。 （正确 ✔）

--新增数据字段注释：

EXECUTE sp_addextendedproperty  'MS_Description', '推送结果', 'user', 'dbo', 'table', 'payment_details', 'column', 'push_result';

EXECUTE sp_addextendedproperty  'MS_Description', '推送信息备注', 'user', 'dbo', 'table', 'payment_details', 'column', 'push_result_remark';

 

 

--查看注释

SELECT

A.name AS table_name,

B.name AS column_name,

C.value AS column_description

FROM sys.tables A

INNER JOIN sys.columns B ON B.object_id = A.object_id

LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id

WHERE A.name = 'payment_details'

 

 

--查看表结构基础信息

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,'&lt;','<')
set @value = replace(@value,'&gt;','>')
set @value= replace( @value,'&#x0D;','')

--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)

 

✔