SQLServer技巧

1. 查询所有表行数及内容占用空间大小，

   SELECT 
       t.NAME AS TableName,
       p.rows AS RowCounts,
       SUM(a.total_pages) * 8 AS TotalSpaceKB,
       SUM(a.used_pages) * 8 AS UsedSpaceKB,
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
   FROM 
       sys.tables AS t
   INNER JOIN      
       sys.indexes AS i ON t.OBJECT_ID = i.object_id
   INNER JOIN 
       sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id
   INNER JOIN 
       sys.allocation_units AS a ON p.partition_id = a.container_id
   WHERE 
       t.is_ms_shipped = 0 AND i.index_id IN (0, 1)
   GROUP BY 
       t.Name, p.Rows
   ORDER BY 
       TotalSpaceKB DESC;
   

2.  与时俱进，在Linux下运行 SQLServer,

   
   # 参考 
   # https://github.com/microsoft/mssql-docker 
   # https://learn.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-docker?view=sql-server-ver16&tabs=cli&pivots=cs1-bash
   # 
   # @SQLServer2019 是密码，记得改
   docker pull mcr.microsoft.com/mssql/server:2019-latest
   docker run  -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=@SQLServer2019" -p 1433:1433 -v /mnt:/mnt   --memory 3072M --name sql2019 -d mcr.microsoft.com/mssql/server:2019-latest
   
   

    

3. 常用语句

#1. 创建数据库 kingdee
CREATE DATABASE kingdee;

#2. 创建登录用户 kingdee
CREATE LOGIN kingdee WITH PASSWORD = 'kingdee', CHECK_POLICY = OFF;
CREATE LOGIN king_reader WITH PASSWORD = 'king_reader', CHECK_POLICY = OFF;;

#3. 将登录用户 kingdee 映射到数据库 kingdee 并设置为 dbo
USE kingdee;
CREATE USER kingdee FOR LOGIN kingdee;
EXEC sp_addrolemember 'db_owner', 'kingdee';

#4. 验证设置
USE kingdee;
SELECT name, type_desc, default_schema_name 
FROM sys.database_principals 
WHERE name = 'kingdee';