June 23, 2011 at 1:14 pm
Anyone know of a way(i.e. UDF, CTE, something) to get the same data that sp_HelpDB returns but in away so that the DB size is a number (and not text with the MB characters added) and each DB is listed as one row no matter how many files the DB has?
UPDATE: Also can return the date in a format/way that can be easily converted to a real date in a sprreadsheet. The date (as text) returned by HelpDB is unrecognizable in excel using standard Date COnvert function.
I thought I had found a handy piece of code that populates a temp table using the uncodcumented ForEachDB SP but it lists each file individually and I'm trying to get a simple summary (1 row per DB).
Even if you just have a link to a story/article on how to do this I woudl be grateful.
Kindest Regards,
Just say No to Facebook!June 23, 2011 at 2:24 pm
Get Server Database File Information
June 23, 2011 at 2:40 pm
Why not just create another version of sp_helpdb based on the system stored proc? Something like this:
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_help_db] -- 1995/12/20 15:34 #12755
@dbname sysname = NULL-- database name
as
declare @exec_stmt nvarchar(625)
declare @showdevbit
declare @name sysname
declare @cmdnvarchar(285) -- (26 + 258) + 1 extra
declare @dbdesc varchar(600)/* the total description for the db */
declare @propdesc varchar(40)
set nocount on
/*Create temp table before any DMP to enure dynamic
** Since we examine the status bits in sysdatabase and turn them
** into english, we need a temporary table to build the descriptions.
*/
create table #spdbdesc
(
dbname sysname,
owner sysname null,
created datetime,
dbidsmallint,
dbdescnvarchar(600)null,
dbsizenvarchar(13) null,
cmptleveltinyint
)
/*
** If no database name given, get 'em all.
*/
if @dbname is null
select @showdev = 0
else select @showdev = 1
/*
** See if the database exists
*/
if not exists (select * from master.dbo.sysdatabases
where (@dbname is null or name = @dbname))
begin
raiserror(15010,-1,-1,@dbname)
return (1)
end
/*
** Initialize #spdbdesc from sysdatabases
*/
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
select name, isnull(suser_sname(sid),'~~UNKNOWN~~'), crdate,
dbid, cmptlevel from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)
declare ms_crs_c1 cursor global for
select db_name (dbid) from #spdbdesc
open ms_crs_c1
fetch ms_crs_c1 into @name
while @@fetch_status >= 0
begin
if (has_dbaccess(@name) <> 1)
begin
delete #spdbdesc where current of ms_crs_c1
raiserror(15622,-1,-1, @name)
end
else
begin
/* Insert row for each database */
select @exec_stmt =
'update #spdbdesc
/*
** 8 KB pages is 128 per MB. If we ever change page size, this
** will be variable by DB or file or filegroup in some manner
** unforseeable now so just hard code it.
*/
set dbsize = (select str(sum(convert(dec(17,2),size)) / 128,10,2) from '
+ quotename(@name, N'[')
+ N'.dbo.sysfiles)
WHERE current of ms_crs_c1'
execute (@exec_stmt)
end
fetch ms_crs_c1 into @name
end
deallocate ms_crs_c1
/*
** Now for each dbid in #spdbdesc, build the database status
** description.
*/
declare @curdbid smallint/* the one we're currently working on */
/*
** Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid) from #spdbdesc
while @curdbid IS NOT NULL
begin
set @name = db_name(@curdbid)
-- These properties always available
SELECT @dbdesc = 'Status=' + convert(sysname,DatabasePropertyEx(@name,'Status'))
SELECT @dbdesc = @dbdesc + ', Updateability=' + convert(sysname,DatabasePropertyEx(@name,'Updateability'))
SELECT @dbdesc = @dbdesc + ', UserAccess=' + convert(sysname,DatabasePropertyEx(@name,'UserAccess'))
SELECT @dbdesc = @dbdesc + ', Recovery=' + convert(sysname,DatabasePropertyEx(@name,'Recovery'))
SELECT @dbdesc = @dbdesc + ', Version=' + convert(sysname,DatabasePropertyEx(@name,'Version'))
-- These props only available if db not shutdown
IF DatabaseProperty(@name, 'IsShutdown') = 0
BEGIN
SELECT @dbdesc = @dbdesc + ', Collation=' + convert(sysname,DatabasePropertyEx(@name,'Collation'))
SELECT @dbdesc = @dbdesc + ', SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@name,'SQLSortOrder'))
END
-- These are the boolean properties
IF DatabasePropertyEx(@name,'IsAutoClose') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoClose'
IF DatabasePropertyEx(@name,'IsAutoShrink') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoShrink'
IF DatabasePropertyEx(@name,'IsInStandby') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsInStandby'
IF DatabasePropertyEx(@name,'IsTornPageDetectionEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled'
IF DatabasePropertyEx(@name,'IsAnsiNullDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault'
IF DatabasePropertyEx(@name,'IsAnsiNullsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled'
IF DatabasePropertyEx(@name,'IsAnsiPaddingEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled'
IF DatabasePropertyEx(@name,'IsAnsiWarningsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled'
IF DatabasePropertyEx(@name,'IsArithmeticAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled'
IF DatabasePropertyEx(@name,'IsAutoCreateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics'
IF DatabasePropertyEx(@name,'IsAutoUpdateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics'
IF DatabasePropertyEx(@name,'IsCloseCursorsOnCommitEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled'
IF DatabasePropertyEx(@name,'IsFullTextEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled'
IF DatabasePropertyEx(@name,'IsLocalCursorsDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault'
IF DatabasePropertyEx(@name,'IsNullConcat') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNullConcat'
IF DatabasePropertyEx(@name,'IsNumericRoundAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled'
IF DatabasePropertyEx(@name,'IsQuotedIdentifiersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled'
IF DatabasePropertyEx(@name,'IsRecursiveTriggersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled'
IF DatabasePropertyEx(@name,'IsMergePublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsMergePublished'
IF DatabasePropertyEx(@name,'IsPublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsPublished'
IF DatabasePropertyEx(@name,'IsSubscribed') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSubscribed'
IF DatabasePropertyEx(@name,'IsSyncWithBackup') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup'
update #spdbdesc set dbdesc = @dbdesc where dbid = @curdbid
/*
** Now get the next, if any dbid.
*/
select @curdbid = min(dbid) from #spdbdesc where dbid > @curdbid
end
/*
** Now #spdbdesc is complete so we can print out the db info
*/
select name = dbname,
db_size = dbsize,
owner = owner,
dbid = dbid,
created = created,
status = dbdesc,
compatibility_level = cmptlevel
from #spdbdesc
order by dbname
/*
** If we are looking at one database, show its file allocation.
*/
if @showdev = 1 and has_dbaccess(@dbname) = 1
begin
print N' '
select @cmd = N'use ' + quotename(@dbname) + N' exec sys.sp_helpfile'
exec (@cmd)
end
return (0) -- sp_helpdb
June 27, 2011 at 2:38 pm
Michael Valentine Jones (6/23/2011)
Get Server Database File Information
Thanks for the link. I have a possibly dumb question though. When testing the code out I ran into some odd problems. Is there anyt issue with white spaces and using sp_msforeachdb ? I discovered that if I tried to shift the DML around (to align certain sections in my text editor) that the code will error out.
Kindest Regards,
Just say No to Facebook!June 27, 2011 at 3:27 pm
YSLGuru (6/27/2011)
Michael Valentine Jones (6/23/2011)
Get Server Database File InformationThanks for the link. I have a possibly dumb question though. When testing the code out I ran into some odd problems. Is there anyt issue with white spaces and using sp_msforeachdb ? I discovered that if I tried to shift the DML around (to align certain sections in my text editor) that the code will error out.
Not sure I understand your question.
Are you saying that the code I posted errored out, or it errored out after you made changes to it?
August 10, 2011 at 3:43 pm
Michael Valentine Jones (6/27/2011)
YSLGuru (6/27/2011)
Michael Valentine Jones (6/23/2011)
Get Server Database File InformationThanks for the link. I have a possibly dumb question though. When testing the code out I ran into some odd problems. Is there anyt issue with white spaces and using sp_msforeachdb ? I discovered that if I tried to shift the DML around (to align certain sections in my text editor) that the code will error out.
Not sure I understand your question.
Are you saying that the code I posted errored out, or it errored out after you made changes to it?
It errors out not after i changed your code but after I replace every TAB with SPACE characters instead.
I did some testing and its a problem with how many SPACE characters are used to replace each TAB. The TABs are equivalent to 2 SPACE characters but if I replace the TABs with 2 SPACE characters each I get errors however if I replace each TAB with a single SPACE the code works.
Perhaps that sounds logical I don't know. This is the first time I've had this problem when replacing a TAB with a SPACE even with 2 SPACEs except of course when working with Character manipulations.
Kindest Regards,
Just say No to Facebook!Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply