我无法将输出数据加载到全局临时表。 [英] I am unable load the output data to a global temp table.
本文介绍了我无法将输出数据加载到全局临时表。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好,
我无法将数据导入全局临时表格。下面给出的查询。谁能帮助我完成它。提前谢谢
如果 存在( 选择 * 来自 tempdb.sys.all_objects 其中 name like ' %#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar (< span class =code-digit> 50 ),Recovery_Model varchar ( 40 )< span class =code-keyword> default (' NA'),file_Size_MB < span class =code-keyword> decimal ( 30 , 2 )默认( 0 ),Space_Used_MB decimal ( 30 , 2 ) default ( 0 ),Free_Space_MB decimal ( 30 , 2 )默认( 0 ))
go
insert 进入 #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
' use [?];
选择DB_NAME()AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''''',''Status'')),
CONVERT(varchar(20),DatabasePropertyEx (''?'',''恢复'')),
总和(大小)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(名称,''SpaceUsed'')AS INT)) /128.0 as Space_Used_MB,
SUM(size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'')AS INT))/ 128.0 AS Free_Space_MB
来自sys.database_files,其中type = 0按类型分组'
go
if 存在(选择 * 来自 tempdb.sys .all_objects 其中名称喜欢 ' #logsize%')
drop table #logsize
cr eate table #logsize
(Dbname sysname,Log_File_Size_MB decimal ( 38 , 2 )默认( 0 ),log_Space_Used_MB 十进制( 30 , 2 )默认( 0 ),log_Free_Space_MB decimal ( 30 , 2 )默认( 0 ))
go
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
' 使用[?];
选择DB_NAME()AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name,''SpaceUsed'')AS INT))/ 128.0 as log_Space_Used_MB ,
SUM(大小)/128.0 - 总和(CAST(FILEPROPERTY(名称,''SpaceUsed'')AS INT))/ 128.0 AS log_Free_Space_MB
来自sys.database_files其中type = 1 group by type'
go
if 存在(选择 * 来自 tempdb.sys.all_objects 其中 name like ' %#dbfreesize%')
drop table #dbfreesize
创建 表 #dbfreesize
(名称sysname,
database_size < span class =code-keyw ord> varchar ( 50 ),
Freespace varchar ( 50 )默认( 0 。 00 ))
插入 进入 #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
' use [?]; SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15,2),dbsize)+ convert(DECIMAL(15,2) ),logsize))* 8192 / 1048576,15,2)+''MB'')
,''unallocated space''= ltrim(str((
CASE
WHEN dbsize> ; = reservedpages
THEN(转换(DECIMAL(15,2),dbsize) - 转换(DECIMAL(15,2),reservedpages))* 8192/1048576
ELSE 0
结束
),15,2)+''MB'')
FROM(
SELECT dbsize = sum(转换(BIGINT,CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(转换(BIGINT,CASE
WHEN type<> 0
THEN size
ELSE 0
END))
FROM sys.database_files
)AS文件
,(
SELECT reservedpages = sum( a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN(
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type<> 1
THEN a.used_pages
WHEN p.index_id< 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
)AS partitions'
如果 存在(选择 * 来自 tempdb.sys.all_objects 其中名称喜欢 ' %#alldbstate%')
drop table #alldbstate
create table #alldbstate
(dbname sysname,
DBstatus varchar ( 55 ),
R_model Varchar ( 30 ))
insert < span class =code-keyword> into #alldbstate(dbname,DBstatus,R_model)
选择名称, CONVERT ( varchar ( 20 ), DATABASEPROPERTYEX (name,' status')),recovery_model_desc 来自 sys.databases
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
选择 dbname,dbstatus,R_model 来自 #alldbstate 其中 DBstatus<> ' online'
insert into #logsize(Dbname)
select dbname 来自 #alldbstate 其中 DBstatus<> ' online'
insert into #dbfreesize(name)
select dbname 来自 #alldbstate 其中 DBstatus<> ' online'
select d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB, d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace,
((d。 Space_Used_MB / d.file_size_mb)* 100) as [Database_Space_used%],
((log_Space_Used_MB / l.Log_File_Size_MB)* 100) as [Log_Space_used%]
into ## final
from #dbsize d join #logsize l
on d.Dbname = l。 Dbname join #dbfreesize fs
on d.Dbname = fs.name
< span class =cod电子关键字>订单 按 Dbname
选择 * 来自 ## final
drop table ## final
解决方案
嘿
对不起..
我我在这里收到错误..
选择 d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB ,l.log_Free_Space_MB,fs.Freespace as DB_Freespace,
((d.Space_Used_MB / d.file_size_mb)* 100) as [Database_Space_used%],
((log_Space_Used_MB / l.Log_File_Size_MB)* 100) as [Log_Space_used%]
到## final
我通过添加ISNULL解决了
选择
d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace
(ISNULL(d.Space_Used_MB / NULLIF(d.file_size_mb, 0 ), 0 )* 100) as [Database_Space_used%],
(ISNULL(log_Space_Used_MB / NULLIF(l.Log_File_Size_MB, 0 ), 0 )* 100) as [Log_Space_used% ]
into ## final
from #dbsize d join #logsize l
on d.Dbname = l.Dbname join #dbfreesize fs
on d.Dbname = fs.name
订单 按 Dbname
Hello,
I am unable to loamd data into a global temp taberle. The query given below. Could anyone please help me to get it done. thanks in advance
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%')
drop table #dbsize
create table #dbsize
(Dbname sysname,dbstatus varchar(50),Recovery_Model varchar(40) default ('NA'), file_Size_MB decimal(30,2)default (0),Space_Used_MB decimal(30,2)default (0),Free_Space_MB decimal(30,2) default (0))
go
insert into #dbsize(Dbname,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
from sys.database_files where type=0 group by type'
go
if exists (select * from tempdb.sys.all_objects where name like '#logsize%')
drop table #logsize
create table #logsize
(Dbname sysname, Log_File_Size_MB decimal(38,2)default (0),log_Space_Used_MB decimal(30,2)default (0),log_Free_Space_MB decimal(30,2)default (0))
go
insert into #logsize(Dbname,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
'use [?];
select DB_NAME() AS DbName,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
from sys.database_files where type=1 group by type'
go
if exists (select * from tempdb.sys.all_objects where name like '%#dbfreesize%')
drop table #dbfreesize
create table #dbfreesize
(name sysname,
database_size varchar(50),
Freespace varchar(50)default (0.00))
insert into #dbfreesize(name,database_size,Freespace)
exec sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_tables it
ON p.object_id = it.object_id
) AS partitions'
if exists (select * from tempdb.sys.all_objects where name like '%#alldbstate%')
drop table #alldbstate
create table #alldbstate
(dbname sysname,
DBstatus varchar(55),
R_model Varchar(30))
insert into #alldbstate (dbname,DBstatus,R_model)
select name,CONVERT(varchar(20),DATABASEPROPERTYEX(name,'status')),recovery_model_desc from sys.databases
insert into #dbsize(Dbname,dbstatus,Recovery_Model)
select dbname,dbstatus,R_model from #alldbstate where DBstatus <> 'online'
insert into #logsize(Dbname)
select dbname from #alldbstate where DBstatus <> 'online'
insert into #dbfreesize(name)
select dbname from #alldbstate where DBstatus <> 'online'
select d.Dbname,d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace ,
((d.Space_Used_MB/d.file_size_mb)*100) as [Database_Space_used%],
((log_Space_Used_MB/l.Log_File_Size_MB)*100)as [Log_Space_used%]
into ##final
from #dbsize d join #logsize l
on d.Dbname=l.Dbname join #dbfreesize fs
on d.Dbname=fs.name
order by Dbname
select * from ##final
drop table ##final
解决方案
Hey
Sorry..
I am getting error here..
select d.Dbname,d.dbstatus,d.Recovery_Model, (file_size_mb + log_file_size_mb) as DBsize, d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace , ((d.Space_Used_MB/d.file_size_mb)*100) as [Database_Space_used%], ((log_Space_Used_MB/l.Log_File_Size_MB)*100)as [Log_Space_used%] into ##final
and I resolved by adding ISNULL
select d.Dbname,d.dbstatus,d.Recovery_Model, (file_size_mb + log_file_size_mb) as DBsize, d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB, l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace (ISNULL(d.Space_Used_MB/NULLIF(d.file_size_mb,0),0)*100) as [Database_Space_used%], (ISNULL(log_Space_Used_MB/NULLIF(l.Log_File_Size_MB,0),0)*100)as [Log_Space_used%] into ##final from #dbsize d join #logsize l on d.Dbname=l.Dbname join #dbfreesize fs on d.Dbname=fs.name order by Dbname
这篇关于我无法将输出数据加载到全局临时表。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文