常用sqlserver语句

一、常用sql语句

--查询当前test1库下的所有表和元信息等
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    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 
    [test1].sys.tables t
INNER JOIN      
    [test1].sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    [test1].sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    [test1].sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    [test1].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 
    t.Name;



-- 查询表名在哪个库下
exec sp_MSforeachdb @command1='USE ? if exists(SELECT 1 from sysobjects where id=object_id(''表名'')) PRINT ''?'''

-- 查看内存使用情况sys.dm_os_memory_clerks
select * from  sys.dm_os_memory_clerks
order by pages_kb desc;

select type,sum(pages_kb)/1024  使用页_M
,sum(virtual_memory_reserved_kb)/1024 内存预留_M
,sum(virtual_memory_committed_kb)/1024 Commited
,sum(awe_allocated_kb)/1024 AWE_Allocated
,sum(shared_memory_reserved_kb)/1024 Shared_Reserved
,sum(shared_memory_committed_kb)/1024 Shared_Commited
from sys.dm_os_memory_clerks
group by type
order by 使用页_M desc;

-- ip信息位于sys.dm_exec_connections表内
select * from sys.dm_exec_connections

-- 读写追踪ip地址
select  
c.session_id,most_recent_session_id,connect_time,net_transport,protocol_type,
protocol_version,c.endpoint_id,c.encrypt_option,auth_scheme,node_affinity,num_reads,
num_writes,last_read,last_write,net_packet_size,client_net_address,client_tcp_port,
local_net_address,local_tcp_port,connection_id,most_recent_sql_handle,login_time,
host_name,program_name,host_process_id,client_version,client_interface_name,security_id,
login_name,nt_domain,nt_user_name,status,context_info,cpu_time,memory_usage,total_scheduled_time,
total_elapsed_time,s.endpoint_id  as s_endpoint_id,last_request_start_time,last_request_end_time,reads,writes,logical_reads,text_size
from  sys.dm_exec_connections c JOIN sys.dm_exec_sessions s  ON s.session_id = c.session_id;

-- 简写：
select  
num_writes 写数据,last_write 最后写时间,client_net_address IP地址1,client_tcp_port IP地址2,
local_net_address IP地址3,
total_elapsed_time,last_request_start_time,last_request_end_time,reads,writes,logical_reads,text_size
from  sys.dm_exec_connections c JOIN sys.dm_exec_sessions s  ON s.session_id = c.session_id;

# 选择显示表的前50%
select top 50 percent * FROM 表名
select top (6) with ties * from 成绩信息 order by 分数
使用with ties的时候后面需要紧跟order by子句，这样使用with ties之后就可以将最后几条值相同记录的信息都显示出来，不会只是显示前6条记录了。

# 利用ROW_NUMBER() OVER(order by 字段)实现limit分页功能
With tempTb AS (
Select ROW_NUMBER() OVER(order by 单号) AS RowNumber,* FROM 表名
) Select * FROM tempTb Where RowNumber>1000

# 选择insert数据
insert into 表1(字段1,字段2...) SELECT 字段1,字段2... FROM 表2

insert into 表1(字段...) (SELECT 字段 FROM 表2 union SELECT 字段 FROM 表3) as tempT

# 联表update
UPDATE a set a.字段1 = b.字段2
from 表1 a inner join 
(select 字段2 from 表2 b where 条件) b 
on a.字段名 = b.字段名

二、查看状态语句

--查看镜像或非正常状态数据库大小
select DB_NAME(mf.database_id) as DatabaseName,
    mf.type_desc as FileType,
    mf.name as FileLogicName,
    mf.physical_name as FilePhysicalName,
    mf.size as PagesCount,
    mf.size*8/1024  as Size_MB,
    mf.size*8/1024/1024.0 as Size_GB
from sys.master_files mf
where mf.database_id= db_id(N'BI')

--查看当前阻塞和死锁的语句
DECLARE @spid INT,
            @bl INT,
            @intTransactionCountOnEntry INT,
            @intRowcount INT,
            @intCountProperties INT,
            @intCounter INT

CREATE TABLE #tmp_lock_who
(
              id INT IDENTITY(1, 1),
              spid SMALLINT,
              bl SMALLINT
)

INSERT INTO #tmp_lock_who ( spid, bl )
SELECT 0, blocked
FROM(SELECT*
FROM sys.sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS(SELECT*
FROM(SELECT*
FROM sys.sysprocesses
WHERE blocked > 0
) b
WHERE a.blocked = spid )
UNION
SELECT spid, blocked
FROM sys.sysprocesses
WHERE blocked > 0

-- 找到临时表的记录数
SELECT @intCountProperties =COUNT(*), @intCounter = 1
FROM #tmp_lock_who

IF @intCountProperties = 0
SELECT N'现在没有阻塞和死锁信息'ASmessage
-- 循环开始
WHILE @intCounter <= @intCountProperties
BEGIN
-- 取第一条记录
SELECT @spid = spid, @bl = bl
FROM #tmp_lock_who
WHERE Id = @intCounter
BEGIN
IF @spid = 0
SELECT N'引起数据库死锁的是: '+CAST(@bl AS VARCHAR(10))
+ N'进程号,其执行的SQL语法如下'
ELSE
SELECT N'进程号SPID：'+CAST(@spid AS VARCHAR(10))
+ N'被进程号SPID：'+CAST(@bl AS VARCHAR(10)) N'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
END
-- 循环指针下移
SET @intCounter = @intCounter + 1
END
DROP TABLE #tmp_lock_who