SqlServer数据库常用脚本合集-持续更新

Libby博仙

已于 2024-08-15 20:28:48 修改

阅读量1.9k

点赞数 1

分类专栏： sqlserver 文章标签：数据库 sqlserver database

于 2022-04-02 09:54:58 首次发布

本文链接：https://blog.youkuaiyun.com/qq_41192896/article/details/123913496

版权

sqlserver 专栏收录该内容

15 篇文章

订阅专栏

关键字查询存储过程

SELECT      DISTINCT
            obj.type AS xtype,
            sch.name AS schema_name,
            obj.name AS objname,
            sc.text
FROM        syscomments sc
            INNER JOIN sys.objects obj ON sc.id = obj.object_id
            INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE       sc.text LIKE '%Company%'
ORDER BY    obj.type,
            sch.name,
            obj.name;

查询当前正在执行的SQL

SELECT TOP 100
 t.hostname,
 t.loginame,
 percent_complete,
 [session_id],
 der.[request_id],
 [start_time] AS '开始时间',
 der.[status] AS '状态',
 [command] AS '命令',
 dest.[text] AS 'sql语句',
 DB_NAME([database_id]) AS '数据库名',
 [blocking_session_id] AS '正在阻塞其他会话的会话ID',
 [wait_type] AS '等待资源类型',
 [wait_time] AS '等待时间',
 [wait_resource] AS '等待的资源',
 [reads] AS '物理读次数',
 [writes] AS '写次数',
 [logical_reads] AS '逻辑读次数',
 [row_count] AS '返回结果行数',
 t.hostname,
 t.loginame
FROM sys.[dm_exec_requests] AS der
 CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
 LEFT JOIN sys.sysprocesses t
 ON t.spid = der.session_id
WHERE [session_id] > 50
-- AND DB_NAME(der.[database_id]) = 'gposdb'
ORDER BY [cpu_time] DESC;

查询所有的库名

SELECT Name FROM Master..SysDatabases ORDER BY Name

查询所有的表名

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name

查询所有的列信息

SELECT      S.name 架构名,
            D.name 表名,
            A.colorder 字段序号,
            A.name 字段名,
            (CASE
                 WHEN COLUMNPROPERTY(A.id, A.name, 'IsIdentity') = 1 THEN
                     '√'
                 ELSE
                     ''
             END) 标识,
            (CASE
                 WHEN (SELECT   COUNT(*)
                       FROM     sysobjects
                       WHERE    (name IN( SELECT    name
                                          FROM      sysindexes
                                          WHERE     (id = A.id)
                                                    AND (indid IN( SELECT   indid
                                                                   FROM     sysindexkeys
                                                                   WHERE    (id = A.id) AND (colid IN( SELECT   colid FROM  syscolumns WHERE(id = A.id) AND (name = A.name)))))))
                                AND   (xtype = 'PK')) > 0 THEN
                     '√'
                 ELSE
                     ''
             END) 主键,
            B.name 类型,
            A.length 占用字节数,
            COLUMNPROPERTY(A.id, A.name, 'PRECISION') AS 长度,
            ISNULL(COLUMNPROPERTY(A.id, A.name, 'Scale'), 0) AS 小数位数,
            (CASE
                 WHEN A.isnullable = 1 THEN
                     '√'
                 ELSE
                     ''
             END) 允许空,
            ISNULL(E.text, '') 默认值,
            ISNULL(G.[value], ' ') AS [说明],
            S.*
FROM        syscolumns A
            LEFT JOIN systypes B ON A.xtype = B.xusertype
            LEFT JOIN sysobjects D ON A.id = D.id
            LEFT JOIN syscomments E ON A.cdefault = E.id
            LEFT JOIN sys.extended_properties G ON A.id = G.major_id AND   A.colid = G.minor_id
            LEFT JOIN sys.extended_properties F ON D.id = F.class AND  F.minor_id = 0
            LEFT JOIN sys.objects O ON O.object_id = A.id
            LEFT JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE       D.xtype = 'U' AND   D.name <> 'dtproperties' AND A.name NOT IN ( '__$start_lsn', '__$end_lsn', '__$seqval', '__$operation', '__$update_mask' )
            AND  D.name = @TableName
ORDER BY    A.id,
            A.colorder;

查询所有数据库的大小，并且按照大小排序

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    CAST(SUM(size * 8 / 1024.0) AS DECIMAL(10, 2)) AS DatabaseSizeMB
FROM 
    sys.master_files
WHERE 
    type = 0  -- 数据文件
GROUP BY 
    database_id
ORDER BY 
    DatabaseSizeMB DESC;

查询当前库所有表的数据总条数count()，按照条数排序

SELECT 
    t.name AS TableName,
    SUM(p.rows) [RowCount]
FROM 
    sys.tables AS t
INNER JOIN 
    sys.partitions AS p ON t.object_id = p.object_id
WHERE 
    p.index_id < 2
GROUP BY 
    t.name
ORDER BY 
    [RowCount] DESC;

跨服务器查询，openRowset支持跨服务器查询，当做子查询表，比如关联查询，select into 时会很方便

SELECT *
FROM OPENROWSET('SQLOLEDB', '192.168.xxx.xx'; 'sa'; 'password', 'SELECT * FROM 表名')
WHERE 1 = 1

修改数据库名称

USE master
GO

exec sp_renamedb 原库名, 新库名

删除所有进程

USE [master]
GO
--存在同名的删除掉
IF EXISTS ( SELECT *
 FROM dbo.sysobjects
 WHERE id = OBJECT_ID(N'[dbo].[p_killspid]')
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
 DROP PROCEDURE [dbo].[p_killspid_by_Libby];
GO
--创建存储过程
CREATE PROC [p_killspid_by_Libby] @dbname VARCHAR(200) --要关闭进程的数据库名
AS
 DECLARE @sql NVARCHAR(500); 
DECLARE @spid NVARCHAR(20);

DECLARE #tb CURSOR
FOR
    SELECT  spid = CAST(spid AS VARCHAR(20))
    FROM    master..sysprocesses
    WHERE   dbid = DB_ID(@dbname);
OPEN #tb;
FETCH NEXT FROM #tb INTO @spid;
WHILE @@fetch_status = 0
    BEGIN 
        EXEC('kill '+@spid);
        FETCH NEXT FROM #tb INTO @spid;
    END; 
CLOSE #tb;
DEALLOCATE #tb;

GO

--用法 
EXEC [p_killspid_by_Libby] 你要操作的数据库名称;
--删除存储过程
DROP PROCEDURE [dbo].[p_killspid_by_Libby];

还原数据库，这里脚本是含分区的，不分区的话只有两个文件，删除多余的move to即可

USE master;
GO
 
RESTORE DATABASE [DataBaseName02]  --新库名
FROM DISK = 'G:\DBFile\DBback\DataBaseName.bak' --备份文件路径
WITH REPLACE,
     MOVE 'DataBaseName' --备份文件中的文件名
     TO 'G:\DBFile\DataBaseFile\DataBaseName02.mdf', -- 还原后的该文件位置
     MOVE 'DataBaseName_log'
     TO 'G:\DBFile\DataBaseFile\DataBaseName02_log.ldf',
     MOVE 'DataBaseName_DataS'
     TO 'G:\DBFile\DataBaseFile\DataBaseName02_DataS.mdf',
     MOVE 'DataBaseName_DataSIndex'
     TO 'G:\DBFile\DataBaseFile\DataBaseName02_DataSIndex.mdf';
 
GO

修改表名

--修改表名
EXEC sp_rename @objname = '旧表名', @newname = '新表名'
EXEC sp_rename '旧表名', '新表名'
 
--例1 把表TABLE1改为TABLE2
EXEC sp_rename @objname = 'TABLE1', @newname = 'TABLE2'
 
--例2（简写）把表TABLE1改为TABLE2
EXEC sp_rename 'TABLE1','TABLE2'

删除所有进程

USE [master]
GO
--存在同名的删除掉
IF EXISTS ( SELECT *
 FROM dbo.sysobjects
 WHERE id = OBJECT_ID(N'[dbo].[p_killspid]')
 AND OBJECTPROPERTY(id, N'IsProcedure') = 1 )
 DROP PROCEDURE [dbo].[p_killspid_by_Libby];
GO
--创建存储过程
CREATE PROC [p_killspid_by_Libby] @dbname VARCHAR(200) --要关闭进程的数据库名
AS
 DECLARE @sql NVARCHAR(500); 
DECLARE @spid NVARCHAR(20);

DECLARE #tb CURSOR
FOR
    SELECT  spid = CAST(spid AS VARCHAR(20))
    FROM    master..sysprocesses
    WHERE   dbid = DB_ID(@dbname);
OPEN #tb;
FETCH NEXT FROM #tb INTO @spid;
WHILE @@fetch_status = 0
    BEGIN 
        EXEC('kill '+@spid);
        FETCH NEXT FROM #tb INTO @spid;
    END; 
CLOSE #tb;
DEALLOCATE #tb;

GO

--用法 
EXEC [p_killspid_by_Libby] 你要操作的数据库名称;
--删除存储过程
DROP PROCEDURE [dbo].[p_killspid_by_Libby];

删除单个进程

--查询出所有进程
SELECT  TOP 100 t.hostname ,
                t.loginame ,
                percent_complete ,
                [session_id] ,
                der.[request_id] ,
                [start_time] AS '开始时间' ,
                der.[status] AS '状态' ,
                [command] AS '命令' ,
                dest.[text] AS 'sql语句' ,
                DB_NAME([database_id]) AS '数据库名' ,
                [blocking_session_id] AS '正在阻塞其他会话的会话ID' ,
                [wait_type] AS '等待资源类型' ,
                [wait_time] AS '等待时间' ,
                [wait_resource] AS '等待的资源' ,
                [reads] AS '物理读次数' ,
                [writes] AS '写次数' ,
                [logical_reads] AS '逻辑读次数' ,
                [row_count] AS '返回结果行数' ,
                t.hostname ,
                t.loginame
FROM    sys.[dm_exec_requests] AS der
        CROSS APPLY sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
        LEFT JOIN sys.sysprocesses t
                  ON t.spid=der.session_id
WHERE   [session_id]>50
-- AND DB_NAME(der.[database_id]) = 'gposdb'
ORDER BY [cpu_time] DESC;


KILL 【session_id】

收缩数据库日志

USE [master];

------------遍历所有用户数据库 进行日志清理------------------------------------------------------------------- 
DECLARE @SQL AS NVARCHAR (4000);
DECLARE @DATABaseName AS NVARCHAR (50);

DECLARE My_Cursor CURSOR --定义游标 
FOR(
SELECT  name
FROM    sys.databases
WHERE   name NOT IN ( 'master', 'msdb', 'tempdb', 'model', 'ReportServerTempDB', 'ReportServer' )); --查出需要的集合放到游标中

OPEN My_Cursor; --打开游标

FETCH NEXT FROM My_Cursor
INTO @DATABaseName; --读取第一行数据 

WHILE @@FETCH_STATUS=0
    BEGIN
        SET @SQL='
USE '+  @DATABaseName +'--查询出数据库对应的日志文件名称
DECLARE @strDBName AS NVARCHAR(500)
DECLARE @strLogName AS NVARCHAR(500)
DECLARE @strSQL AS VARCHAR(1000)

DECLARE @DBLogSise AS INT = 2

SELECT @strLogName = B.name,
  @strDBName = A.name
FROM   MASTER.sys.databases         AS A
  INNER JOIN sys.master_files  AS B
ON  A.database_id = B.database_id
WHERE  A.database_id = DB_ID() 

SET @strSQL = ''
--设置数据库恢复模式为简单
ALTER DATABASE ['' + @strDBName +
''] SET RECOVERY SIMPLE;
--收缩日志文件
DBCC SHRINKFILE ('' + @strLogName + '' , '' + CONVERT(VARCHAR(20), @DBLogSise)
+ '');
--恢复数据库还原模式为完整
ALTER DATABASE ['' + @strDBName + ''] SET RECOVERY FULL ''

EXEC (@strSQL)';

        EXEC ( @SQL );

        PRINT '清理'+@DATABaseName+'日志完成';

        FETCH NEXT FROM My_Cursor
        INTO @DATABaseName; --读取下一行数据
    END;

CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标

PRINT '-----------清理日志完成---------------------'+CONVERT(VARCHAR (100), GETDATE(), 126)+'----------';

收缩数据库实例

USE DataBaseName
GO

SELECT allocated_extent_page_count*8000/1024/1024 结果,* FROM sys.dm_db_file_space_usage

----用allocated_extent_page_count列数据乘8000得出字节----
----用得出字节除两次1024得出MB，填到下面结果里----

DBCC SHRINKFILE('DataBaseName', 结果);