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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy