整理工作中常用的Sql 语句，方便使用时快速查找。

数据库日志文件压缩

ALTER DATABASE 数据库名
SET RECOVERY SIMPLE --数据库恢复模式设置为简单模式

GO

DBCC SHRINKFILE (数据库日志文件逻辑名称,0) --将日志文件压缩为0M，如果是sa用户登录，请先切换到当前数据库后，再执行此条命令收缩。

GO

ALTER DATABASE 数据库名 
SET RECOVERY FULL --还原数据库恢复模式为完整模式。

--以下语句是查询数据库和数据库日志逻辑名称的SQL语句。
SELECT d.name DatabaseName, f.name LogicalName,
f.physical_name AS PhysicalName,
f.type_desc TypeofFile
FROM sys.master_files f
INNER JOIN sys.databases d ON d.database_id = f.database_id
GO 

查询各个表占用的磁盘空间大小

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

SQLServer 2019 Linux版本 更改监听端口

sudo /opt/mssql/bin/mssql-conf set network.tcpport 1450

SQLServer 2019 Linux版本关闭远程连接

EXEC sp_configure 'remote access', 0 ;  
GO  
RECONFIGURE ;  
GO  

SQL Server Linux 版本MSSQL-CLI 自定义端口连接

mssql-cli -S 127.0.0.1,端口号

查询高级配置信息(如允许使用最大的内存)

sp_configure 'show advanced options', 1;
reconfigure;
EXEC sp_configure;  

SQL Server Linux 版本配置允许使用的最大内存

EXEC sys.sp_configure 'max server memory (MB)', '512'
reconfigure with override

SQL Server 统计各个表总记录数

rows列表示表所包含的记录总数。

 select id,object_name(id) as tableName,indid,rows,rowcnt   
from sys.sysindexes where indid in(0,1) order by rows desc

SQL Server 查询指定表包含哪些索引

exec sp_helpindex 表名

SQL Server 查询表索引占用的空间大小

其中IndexSizeKB列表示索引占用的空间大小，单位Kb.

SELECT
i.name                  AS IndexName,
SUM(s.used_page_count) * 8   AS IndexSizeKB
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.indexes                AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('表名')
GROUP BY i.name
ORDER BY i.name

SQL Server 查询表占用的空间大小

exec sp_spaceused '表名'