Sql 常用代码

数据库表和列操作

IF not EXISTS (  
    SELECT 1  
    FROM sys.tables  
    WHERE name = 'Test_UserInfo'  
)  
BEGIN  
	--创建一个表
    create table Test_UserInfo(
		Id int PRIMARY KEY IDENTITY(1, 1),--主键自增
		RealName nvarchar(50),
		IsDel bit default 0,
		Memo nvarchar(50),
		CreateTime datetime default getdate()
	);
END
    GO
    
    --SQL Server 新增一列
    alter table Test_UserInfo add Phone nvarchar(50);
    
    --SQL Server 修改列的类型
    alter table Test_UserInfo alter column IsDel int null;
    
    --SQL Server 修改列名
    EXEC sp_rename 'Test_UserInfo.Memo', 'Memo2', 'COLUMN';
    
    --SQL Server 删除一列
    alter table Test_UserInfo drop column Memo2;
 
	--设置列的备注
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_News', @level2type=N'COLUMN',@level2name=N'AddTime'
    GO
	--设置表的备注
    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'新闻资讯表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T_News'
    GO

数据批量插入另一表（表存在或不存在）

--如果目标表存在：
insert into 目标表 select * from 原表

--如果目标表不存在：
select * into 目标表 from 原表

--如果不想复制数据，仅复制表结构：
select * into 目标表 from 原表 where 1=0

查看数据库中的所有表的名称

Select TABLE_NAME FROM WebAppBase.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' order by TABLE_NAME asc

获取一个表的相关属性

select
col.COLUMN_NAME  as  ColumnName,
col.DATA_TYPE  as  DataType,
col.CHARACTER_OCTET_LENGTH  as  DataLength,
col.IS_NULLABLE  as  IsNullable,
ccu.CONSTRAINT_NAME  as  IsPrimaryKey,
de.value  as  Description
from  INFORMATION_SCHEMA.COLUMNS col
left  join  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
on  ccu.TABLE_NAME=col.TABLE_NAME
and  ccu.COLUMN_NAME=col.COLUMN_NAME
and  ccu.CONSTRAINT_NAME  like  'PK_%'
left  join  ::fn_listextendedproperty ( NULL ,  'user' ,  'dbo' ,  'table' , 'SysAdmin', 'column' ,  default )  as  de
on  col.COLUMN_NAME = de.objname  COLLATE  Chinese_PRC_CI_AS
where  col.TABLE_NAME='SysAdmin'

执行结果示例：

数据库中，从自定义函数、存储过程里查找某个关键词

SELECT a.name,a.[type],b.[definition] 
FROM sys.all_objects a,sys.sql_modules b
WHERE a.is_ms_shipped=0 AND a.object_id = b.object_id AND a.[type] IN ('P','V','AF')
AND b.[definition] LIKE '%pr_%'
ORDER BY a.[name]

执行结果示例

从一个数据库里找到所有包含某个关键字的列，以及它所在的表

SELECT distinct t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%AddTime%'
ORDER BY schema_name, table_name

执行结果示例

sql xml xpath,从多条记录中拼接特定规则的字符串

SELECT ActivityOrderID,OrderSN,OrderTotalAmount,PayTime,GivenSeatNum,AddTime,
(
       SELECT STUFF(
              (SELECT '、' + convert(nvarchar(10),RowNumber)+'排'+CONVERT(nvarchar(10),ColumnNumber)+'号'  
              FROM [dbo].Seat
              where exists(select 1 from  dbo.func_split_str_to_table(GivenSeatIDStr,',',1) t where t.items=Seat.SeatID
              ) FOR  XML PATH('')
              
       ),1,1,'')
)as GivenSeatDesc
FROM dbo.ActivityOrder

其他

--普通时间 转 13 位时间戳
SELECT CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01 00:00:00.000', GETUTCDATE())) * 60000 + DATEPART(S,GETUTCDATE()) * 1000 + DATEPART(MS, GETUTCDATE())
--13位时间戳 转 普通时间
SELECT DATEADD (MS ,CONVERT(BIGINT,1582687127323) % 60000 ,DATEADD(MI,CONVERT(BIGINT,1582687127323) / 60000,'1970-01-01 08:00:00.000'))
--普通时间转换成10位时间戳
SELECT DATEDIFF(S,'1970-01-01 00:00:00', GETDATE())
--10位时间戳转换成普通时间
SELECT DATEADD(S,1557493321,'1970-01-01 00:00:00')

原文链接：https://blog.csdn.net/y281252548/java/article/details/104512936

查看表存储空间占用大小

  exec sp_spaceused 'Task_Member'  --表空间大小
  exec sp_spaceused    --数据库空间大小

---

更新于：2023.5.24