SQL Server个人笔记（2）

#（SQL Server个人笔记2）

s0911数据库还原时-常见问题

Q1：System.Data.SqlClient.SqlError: 因为数据库正在使用，所以无法获得对数据库的独占访问权。 (Microsoft.SqlServer.SmoExtended)
A1：目标数据库(B)还原时-点击确认按钮前一定要看一下 注意：服务器连接 谨慎关闭（关闭到目标数据库的现有连接方
–要考虑到会有那些后果，比如影响其他用户访问（因为此数据库所以连接都已关闭）

s0923删库

use master 
go 
declare @dbname sysname 
set @dbname = 'DB_demo' --填写无法删除的数据库名称

declare @s nvarchar(1000) 
declare tb cursor local 
for 
select s = 'kill ' + cast(spid as varchar) 
from master.dbo.sysprocesses 
where dbid = DB_ID(@dbname) 

open tb 
fetch next from tb into @s 
while @@fetch_status = 0 
begin 
exec (@s) 
fetch next from tb into @s 
end 
close tb 
deallocate tb 

exec ('drop database [' + @dbname + ']')

s0923删库方法2

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'YLD0917' --填写数据库名称，(msdb.dbo.sp_delete_database_backuphistory删除数据库备份和还原历史记录信息)
GO
USE [master]
GO
ALTER DATABASE [YLD0917] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE --设置库单用户模式，和设置立即回滚
GO
USE [master]
GO
DROP DATABASE [YLD0917]
GO

s0924查数据库各表数据量

--查数据库各表数据量
--先创建表

create table #t
	(
	name varchar(255), 
	rows bigint, 
	reserved varchar(20), 
	data varchar(20), 
	index_size varchar(20),
	 unused varchar(20)
	 )

exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'"

--后查各个表的数据大小

select * from #t  order by rows desc

查数据库各表数据量2

CREATE TABLE #tablespaceinfo
    (
      nameinfo VARCHAR(500) ,
      rowsinfo BIGINT ,
      reserved VARCHAR(20) ,
      datainfo VARCHAR(20) ,
      index_size VARCHAR(20) ,
      unused VARCHAR(20)
    );  
  
DECLARE @tablename VARCHAR(255);  
  
DECLARE Info_cursor CURSOR
FOR
    SELECT  '[' + [name] + ']'
    FROM    sys.tables
    WHERE   type = 'U';  
  
OPEN Info_cursor;  
FETCH NEXT FROM Info_cursor INTO @tablename;  
  
WHILE @@FETCH_STATUS = 0
    BEGIN 
        INSERT  INTO #tablespaceinfo
                EXEC sp_spaceused @tablename;  
        FETCH NEXT FROM Info_cursor  
    INTO @tablename;  
    END; 
  
CLOSE Info_cursor;  
DEALLOCATE Info_cursor;  
  
--创建临时表
CREATE TABLE [#tmptb]
    (
      TableName NVARCHAR(MAX) ,
      DataInfo BIGINT ,
      RowsInfo BIGINT ,
      Spaceperrow AS ( CASE RowsInfo
                         WHEN 0 THEN 0
                         ELSE CAST(DataInfo AS DECIMAL(18, 2)) / CAST(RowsInfo AS DECIMAL(18, 2))
                       END ) PERSISTED
    );
 
--插入数据到临时表
INSERT  INTO [#tmptb]
        ( [TableName] ,
          [DataInfo] ,
          [RowsInfo]
        )
        SELECT  [nameinfo] ,
                CAST(REPLACE([datainfo], 'KB', '') AS BIGINT) AS 'datainfo' ,
                [rowsinfo]
        FROM    #tablespaceinfo
        ORDER BY CAST(REPLACE(reserved, 'KB', '') AS INT) DESC;  
 
 
--汇总记录
SELECT  [tbspinfo].* ,
        [tmptb].[Spaceperrow] AS '每行记录大概占用空间（KB）'
FROM    [#tablespaceinfo] AS tbspinfo ,
        [#tmptb] AS tmptb
WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]
ORDER BY CAST(REPLACE([tbspinfo].[reserved], 'KB', '') AS INT) DESC;  
 
DROP TABLE [#tablespaceinfo];
DROP TABLE [#tmptb];