sqlserver常用查询语句

--查询语句
SELECT * FROM BASE_USER;
--查询前10条语句
SELECT TOP 10 * FROM BASE_USER;
--允许脏读查询
SELECT * FROM BASE_USER WITH(NOLOCK);
--获取当前数据库名称
SELECT DB_NAME(dbid) as dbName FROM master.dbo.sysprocesses WHERE STATUS='runnable';
--获取当前日期与时间
 SELECT GETDATE();
--获取当前月
 SELECT DATEPART(month, getdate());
--获取当前年
 SELECT DATEPART(year, getdate());
--获取倒推一个月的日期
SELECT CONVERT(varchar(10), CONVERT(varchar(8),dateadd(month,-1,getdate()),23)+Datename(day,GetDate()) , 120);
--字符串截取与定位
SELECT substring(SERVICEGROUP_,4,1)，CHARINDEX('Partner',STAFFLEVEL) FROM BASE_USER;
--字符替换
SELECT REPLACE(A.NAME,'11','22')  FROM TD_STUDENTS;
--检查数据库连接数
select count(*) from master.dbo.sysprocesses ;
--查询数据库版本
SELECT @@VERSION;

--最近七天

select  convert(varchar(10),getdate() - 7,120)；

--表与数据复制

select * into DataTable_20150717
from DataTable

--查询表名

SELECT 'DELETE FROM ' +name+';'  FROM sysobjects   WHERE sysobjects.type='U';

--获取行号
row_number()over(order by A.id)

--拼接列
CONCAT(tc.operate,tc.operaterType)

--检查锁表
 select   request_session_id   spid,OBJECT_NAME(resource_associated_entity_id) tableName
    from   sys.dm_tran_locks t where resource_type='OBJECT' order by spid asc;

--查询表记录数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC

--根据指定字符分割字符串函数
parsename(replace(a.category,',','.'),1)

执行存储过程
EXEC   dbo.getTrTaskGroup

--字符替换
update dbo.base_user set e_name = REPLACE(c_name,'G','zu')

--查询数据库连接
SELECT @@MAX_CONNECTIONS; --最大连接
SELECT * FROM master.dbo.sysprocesses WHERE DB_NAME(dbid) = 'tr';--当前数据库连接信息

--查询alwaysOn当前master名
SELECT @@SERVERNAME, @@SERVICENAME

--查询数据库大小
EXEC sp_spaceused;
select name, convert(float,size) * (8192.0/1024.0)/1024. from  dbo.sysfiles;

--查询sql执行历史记录
SELECT QS.creation_time,    
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,   
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)   
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1   
) AS statement_text  FROM sys.dm_exec_query_stats QS
CROSS APPLY   
sys.dm_exec_sql_text(QS.sql_handle) ST   
ORDER BY   
QS.creation_time DESC

--查询表中所有索引
exec sp_helpindex 表名
--删除索引
drop index 索引名称 on 表名;

--查询sql执行耗时
SELECT  TOP 100
         [Total Cost]  = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
         , avg_user_impact
         , TableName = statement
         , [EqualityUsage] = equality_columns
         , [InequalityUsage] = inequality_columns
         , [Include Cloumns] = included_columns
FROM        sys.dm_db_missing_index_groups g
 INNER JOIN    sys.dm_db_missing_index_group_stats s
        ON s.group_handle = g.index_group_handle
 INNER JOIN    sys.dm_db_missing_index_details d
        ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC;

--行转列