一.迁移登录用户脚本:
select 'create login [' + p.name + '] ' + case when p.type in('U','G') then 'from windows ' else '' end + 'with ' + case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' + 'sid = ' + master.sys.fn_varbintohexstr(l.sid) + ', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end + 'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end + case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end else '' end + 'default_database = ' + p.default_database_name + case when len(p.default_language_name) > 0 then ', default_language = "' + p.default_language_name +'"' else '''' end from sys.server_principals p left join sys.sql_logins l on p.principal_id = l.principal_id left join sys.credentials c on l.credential_id = c.credential_id where p.type in('S','U','G') and p.name <> 'sa'
二.查看数据库阻塞:
SELECT wt.blocking_session_id AS BlockingSessesionId ,sp.program_name AS ProgramName ,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName ,ec1.client_net_address AS ClientIpAddress ,db.name AS DatabaseName ,wt.wait_type AS WaitType ,ec1.connect_time AS BlockingStartTime ,wt.WAIT_DURATION_MS/1000 AS WaitDuration ,ec1.session_id AS BlockedSessionId ,h1.TEXT AS BlockedSQLText ,h2.TEXT AS BlockingSQLText FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
三.查看当前数据库脚本运行情况:
SELECT creation_time N'语句编译时间' ,last_execution_time N'上次执行时间' ,execution_count N'执行次数' ,case datediff(ss,creation_time,last_execution_time) when 0 then 0 else execution_count/datediff(ss,creation_time,last_execution_time) end N'每秒执行次数' ,total_physical_reads N'物理读取总次数' ,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' , total_worker_time/1000 N'所用的CPU总时间ms' , total_elapsed_time/1000 N'总花费时间ms' , (total_elapsed_time / execution_count)/1000 N'平均时间ms' ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) N'执行语句'
,db_name(st.dbid) as dbname,st.objectid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) not like '%fetch%' ORDER BY execution_count DESC;
四.处理Identity列的一些方法:
DBCC CHECKIDENT (xxxxxx, NORESEED) 报告当前表的标识列
DBCC CHECKIDENT (xxxxxx, RESEED, 30) 强制将标识设置成30(如果有主键约束,后续插入可能会失败)。
在标识列插入数据(字段名称要写全)
set identity_insert xxxx on
insert into xxxx (id,a,b,c)
select id,a,b,c
from yyyyy
set identity_insert xxxx on
转自:http://www.cnblogs.com/luck001221/p/4494840.html
#########################################################自己补充#########################################################
列出数据库主体的所有权限
SELECT pr.principal_id, pr.name, pr.type_desc,
pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;
授权语句:
GRANT SELECT ON 表名 (‘字段‘) TO 用户名
grant select on tb(id,name) to username
对库里哪个表哪些字段用哪些权限的查询:
USE CCIE
EXEC sp_helprotect @username='cisco'
某一时间段:
between '2012-03-01' and '2012-03-10 23:59:59.997'
create table T_UserLoginCount(
ID int not null identity(1,1) primary key,
LoginDate date null,
LoginCount int null,
CreateDate datetime null)
查看是否有死锁:
select * from sys.dm_tran_locks
where request_status='lock' 注:把lock换成wait 是查等待
select * from sys.sysprocesses 查看数据库连接数
排序:
order by 充值钱数 asc -- 表示从小到大排序 order by 充值钱数 desc -- 表示从大到小排序
查询数据库的创建时间:
select * from sys.databases
where name in ('数据库名')
查注册IP相同的账号:
with T as(
select CreateIP
from t_users
group by CreateIP having count(1)>1
)
select A.userid,A.UserName,A.NikeName,A.cardid,a.CreateIP,a.LastLoginIp,a.CreateTime
from t_users A
where exists(select 1 from T where A.CreateIP=T.CreateIP) order by CreateIP
某一时间段: between '2012-03-01' and '2012-03-10 23:59:59.997'
查看是否有死锁:select * from sys.dm_tran_locks
where request_status='lock' 注:把lock换成wait 是查等待
select * from sys.sysprocesses 查看数据库连接数
排序: order by 充值钱数 asc -- 表示从小到大排序
order by 充值钱数 desc -- 表示从大到小排序
查询数据库创建时间:select * from sys.databases
where name in ('数据库名')
排重:distinct
自增列定义:identity 主键定义:primary key
按小时求和
select DATEPART(HOUR,createtime) h,sum(input)/1024/1024 s_input,sum(output)/1024/1024 s_output
from dbo.traffic
where createtime between '2016-01-07 12:00:00' and '2016-01-07 23:59:59.997'
group by DATEPART(HOUR,createtime)
查询某一天的:
convert(varchar(10),CompetesTime,120)='2011-01-01'
求一个字段的时间段:
convert(varchar(10),ActualDealTime,120) between '2012-04-11' and '2012-04-19'