JZTXT

Sql 常用代码

发布时间 2023-05-24 11:45:07作者: AI大胜

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

--如果目标表存在：
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'

执行结果示例：

image-20210708081349101

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

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]

执行结果示例

image-20210708081644042

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

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

执行结果示例

image-20210708082028034

创建数据库，修改数据表的字段默认值和字段备注、表备注

CREATE TABLE dbo.T_UserVerificationCode(
        UserCodeID int IDENTITY(1,1) NOT NULL,
        UserTel nvarchar(50) NULL,
        VerificationType int NULL,
        CodeValue nvarchar(50) NULL,
        ExpirationTime datetime NULL,
        AddTime datetime NULL
    CONSTRAINT [PK_T_UserVerificationCode] PRIMARY KEY CLUSTERED
    (
        [UserCodeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[T_News] ADD  DEFAULT (getdate()) FOR [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', @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

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