SQL SERVER库使用语句合集

最新推荐文章于 2024-05-22 02:08:08 发布

Romona_J

最新推荐文章于 2024-05-22 02:08:08 发布

阅读量170

点赞数 2

文章标签：数据库 sqlserver

本文链接：https://blog.csdn.net/Romona_J/article/details/137232108

版权

SQL SERVER库使用语句合集

查看视图的创建语句

sp_helptext '视图名称';

或者

select text from syscomments s1 join sysobjects s2 on s1.id=s2.id where name='视图名称';

查询指定库的所有用户

use 指定库
select name,type_desc from sys.database_principals;

创建用户操作：

1、进入master库：  
use [master];                                                   
select * from sys.sql_logins;    --查询所有login用户                               
																								                
drop user IF EXISTS 用户名                                      
DROP LOGIN  用户名（master中是drop login）                      
																								                
CREATE LOGIN [用户名] WITH PASSWORD='xxxxxx';         
																								                
2、进到指定库                                            
use [指定库];                                              
CREATE USER 用户名                                              
	FOR LOGIN 用户名                                              
	WITH DEFAULT_SCHEMA = [dbo]                                   
3、赋权（在指定库）                                        
ALTER ROLE db_ddladmin ADD MEMBER [用户名]; 		                
ALTER ROLE db_datareader ADD MEMBER [用户名];                   
ALTER ROLE db_datawriter ADD MEMBER [用户名];                   
																						                    
drop user IF EXISTS 用户名                                      
select * from [sys].[database_principals] where name='用户名';

如果为Microsoft asure sqlserver创建用户：

use master;
create login 用户名 with password='fH)f3@MFepA(UjL7';
CREATE USER CNSDCDLRW	FOR LOGIN CNSDCDLRW	WITH DEFAULT_SCHEMA = [dbo]
use 指定库;
create user user名称 from login login用户名;
exec sp_addrolemember 'db_datareader','用户名';

查看当前登录用户

select spid,db_name(dbid) as DBname,login_time ,last_batch ,status ,hostname ,program_name ,loginame   
from sys.sysprocesses 
where spid >50
and loginame <> 'DESKTOP-ABCD\Administrator'
--and dbid in (select dbid from master.dbo.sysdatabases where name ='库名')
order by last_batch desc

查看当前连接数据库的连接

sp_who2

查询未提交的事务

USE master
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT es.session_id, es.login_name, es.host_name, est.text
, cn.last_read, cn.last_write, es.program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st --系统里还存在的事务
ON es.session_id = st.session_id
INNER JOIN sys.dm_exec_connections cn
ON es.session_id = cn.session_id
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id

AND er.session_id IS NULL

查看正在执行的sql语句

SELECT  
der.[session_id],der.[blocking_session_id],  
sp.lastwaittype,sp.hostname,sp.program_name,sp.loginame,  
der.[start_time] AS '开始时间',  
der.[status] AS '状态',  
dest.[text] AS 'sql语句',  
DB_NAME(der.[database_id]) AS '数据库名',  
der.[wait_type] AS '等待资源类型',  
der.[wait_time] AS '等待时间',  
der.[wait_resource] AS '等待的资源',  
der.[logical_reads] AS '逻辑读次数'  
FROM sys.[dm_exec_requests] AS der  
INNER JOIN master.dbo.sysprocesses AS sp ON der.session_id=sp.spid  
CROSS APPLY  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest  
--WHERE [session_id]>50 AND session_id<>@@SPID  
ORDER BY der.[session_id]  
GO

是否有死锁

 --drop table #deadlock

CREATE TABLE #deadlock(LogDate DATETIME,ProcessInfo VARCHAR(20),Text VARCHAR(2000))  
go  
INSERT INTO #deadlock EXEC xp_readerrorlog 0,1,'deadlock victim',NULL,'2016-07-20 14:00:00','2016-07-30','DESC'    
go

是否堵塞

SELECT spid,blocked,waittime,waittype,waitresource,p.dbid,cpu,physical_io,memusage,open_tran 
,status,login_time,last_batch,hostname,program_name,hostprocess,loginame,cmd,text 
FROM master.dbo.sysprocesses p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) s 
WHERE blocked > 0 OR spid IN(SELECT blocked FROM master.dbo.sysprocesses WHERE blocked > 0) 
go

给数据库重命名操作

一、右键数据库名，选择 “属性”，然后会弹出一个页面，选择左侧的 “选项”按钮，然后在右侧找到 “状态” 栏下的 “限制访问”，将 “限制访问”的值改为 “SINGLE_USER”，然后点击下方的 “确定” 按钮

二、使用 master 权限 执行以下 SQL 语句：

         Exec sp_renamedb'dbold','dbnew'
         go

     其中，dbold为原来的数据库名，dbnew 为新的数据库名

三、1. 执行以下SQL语句把数据库相关进程杀掉：

        USE master; 
        GO 
        DECLARE @SQL VARCHAR(3000);
        SET @SQL = '';
        SELECT @SQL = @SQL+'; KILL ' + RTRIM(SPID)
        FROM [sys].[sysprocesses] AS sps
        WHERE [sps].[dbid] = DB_ID('dbname'); 
        SET @SQL = SUBSTRING(@SQL, 2, LEN(@SQL));
        EXEC(@SQL);
        GO

      其中，dbname 为新的数据库名，即在第二步中修改后的新数据库名

2. 执行以下SQL语句，把数据库权限由单用户设置为多用户：

    ALTER DATABASE [dbname] SET MULTI_USER;--设置为多用户模式

其中，dbname 为新的数据库名，即在第二步中修改后的新数据库名，与第三步中的数据库名一致

获取所有数据库下所有权限的脚本

create table master.dbo.yomi(dbname nvarchar(100),databaseusername nvarchar(100),role nvarchar(50)); ---yomi为我创建的临时储存表

EXEC sp_MSforeachdb
@command1 = '
use [?];
if db_name() not in (''master'',''model'',''msdb'',''tempdb'')
BEGIN

insert into master.dbo.yomi
SELECT [dbname]=db_name(),
[DatabaseUserName] = memberprinc.[name], 
[Role] = roleprinc.[name]
FROM  
sys.database_role_members members 
JOIN 
sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id] 
JOIN 
sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id] 
LEFT JOIN 
sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid] 
LEFT JOIN   
sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id] 
LEFT JOIN 
sys.columns col on col.[object_id] = perm.major_id 
       AND col.[column_id] = perm.[minor_id] 
LEFT JOIN 
sys.objects obj ON perm.[major_id] = obj.[object_id] where memberprinc.sid in (select sid from master.sys.server_principals where type_desc  in (''SQL_LOGIN'',''WINDOWS_GROUP'',''WINDOWS_LOGIN''))
END
'


select distinct 'use ['+dbname+'];'+'CREATE user ['+databaseusername+'] for login ['+databaseusername+'];' from
 master.dbo.yomi where databaseusername not in ('dbo','zcloud_bk');

select 'use ['+dbname+'];'+'EXEC sp_addrolemember '''+role+''' ,'''+databaseusername+''';' from 
 master.dbo.yomi where databaseusername not in ('dbo','zcloud_bk') order by dbname;
 

//select * from  master.dbo.yomi where  dbname='库名' and databaseusername not in ('dbo','zcloud_bk') order by dbname;//这个是查找指定库的所有用户的权限

drop table master.dbo.yomi;

更新数据库的统计信息

USE 库名;      
GO      
EXEC sp_updatestats;

查看用户的权限

SELECT 
    dbuser.name AS UserName,
    ISNULL(dbrole.name, 'Public') AS RoleName
FROM 
    sys.database_principals AS dbuser
LEFT JOIN 
    sys.database_role_members AS dbrolemembers ON dbuser.principal_id = dbrolemembers.member_principal_id
LEFT JOIN 
    sys.database_principals AS dbrole ON dbrolemembers.role_principal_id = dbrole.principal_id
WHERE 
    dbuser.type_desc = 'SQL_USER'
    AND dbuser.name = 'CNSDFDP_FOR_FDP_PROD' -- 替换为你要查询的用户名

后续待补充~