Gathering information about space in a database using transact sq

Hi!,
I want to gather information about the total space, space used and some
others
statistics like the task pad in the enterprise manager, but using a transact
sql script. I didn't find system tables or useful procedures to do that , the
sp_spaceused give some information but I would like to made some statistics
with this values,
any ideas'
thanks!Hi
Run Profiler and Open up taskpad in EM, and see what EM executes agaisnt the
DB. This gives you a good idea of what they are using.
Regards
Mike
"Her" wrote:
> Hi!,
> I want to gather information about the total space, space used and some
> others
> statistics like the task pad in the enterprise manager, but using a transact
> sql script. I didn't find system tables or useful procedures to do that , the
> sp_spaceused give some information but I would like to made some statistics
> with this values,
> any ideas'
> thanks!
>

Gathering information about space in a database using transact sq

Hi!,
I want to gather information about the total space, space used and some
others
statistics like the task pad in the enterprise manager, but using a transact
sql script. I didn't find system tables or useful procedures to do that , th
e
sp_spaceused give some information but I would like to made some statistics
with this values,
any ideas'
thanks!Hi
Run Profiler and Open up taskpad in EM, and see what EM executes agaisnt the
DB. This gives you a good idea of what they are using.
Regards
Mike
"Her" wrote:

> Hi!,
> I want to gather information about the total space, space used and some
> others
> statistics like the task pad in the enterprise manager, but using a transa
ct
> sql script. I didn't find system tables or useful procedures to do that ,
the
> sp_spaceused give some information but I would like to made some statistic
s
> with this values,
> any ideas'
> thanks!
>

Gathering information about space in a database using transact sq

Hi!,
I want to gather information about the total space, space used and some
others
statistics like the task pad in the enterprise manager, but using a transact
sql script. I didn't find system tables or useful procedures to do that , the
sp_spaceused give some information but I would like to made some statistics
with this values,
any ideas?
thanks!
Hi
Run Profiler and Open up taskpad in EM, and see what EM executes agaisnt the
DB. This gives you a good idea of what they are using.
Regards
Mike
"Her" wrote:

> Hi!,
> I want to gather information about the total space, space used and some
> others
> statistics like the task pad in the enterprise manager, but using a transact
> sql script. I didn't find system tables or useful procedures to do that , the
> sp_spaceused give some information but I would like to made some statistics
> with this values,
> any ideas?
> thanks!
>

FWIW: Database Space Used (stored proc)

For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:

1. It returns a single resultset (instead of multiple resultsets);
2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
3. I eliminated the formatting from the result set (the numbers are expressed in KB)

Place the code into an admin database or (more risky and less "best practice") directly into your master database.

Usage:
USE MyDatabase
GO

EXEC AdminDatabase.dbo.sp_dbspaceused
GO

CREATE PROCEDURE sp_dbspaceused

as

declare @.id int -- The object id of @.objname.
declare @.pages int -- Working variable for size calc.
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.logsize dec(15)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

/*
** If @.id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @.dbsize = Pages used
** @.bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @.dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
select @.dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @.logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @.bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @.pagesperMB = 1048576 / @.bytesperpage
/*
select database_name = db_name(),
database_size =
ltrim(str((@.dbsize + @.logsize) / @.pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@.dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @.pagesperMB,15,2)+ ' MB')
*/
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @.pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @.pages = @.pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @.pages

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = cast((reserved * d.low / 1024.) as bigint) ,
data = cast((data * d.low / 1024.) as bigint) ,
index_size = cast((indexp * d.low / 1024.) as bigint) ,
unused = cast((unused * d.low / 1024.) as bigint)
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused

GOI think this one is shorter ;):

select
reserved=(
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255)))*8|||Yes it is. No one ever accused me of having an overabundance of imagination.

Thanks for the nice re-write.

Regards,

hmscott|||With compliments to rdjabarov and apologies to those who do this for a living , I offer up this version which will pull the results for each database...

Regards,

hmscott

ALTER PROC sp_dbSpaceUsed

AS

CREATE TABLE #TempSpace (
[Database] varchar(255),
Reserved dec(15),
Index_Size dec(15),
Data dec(15),
Unused dec(15)
)

DECLARE @.sSQL varchar(1000)

SELECT @.sSQL = 'INSERT INTO #TempSpace ([Database], Reserved, Index_Size, Data, Unused)
SELECT
''?'' as [Database],
reserved=(
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255)))*8'

EXEC sp_MSforeachdb @.command1=@.sSQL

SELECT * FROM #TempSpace

DROP TABLE #TempSpace

Function like Len() that counts white space

Good day to you, community friend!

Just a quick one - I swear I've seen a function before that was like Len() but it counted the white spaces too. I have a feeling it was something along the lines of "data_length" but I can't for the life of me find it!

Many thanks,
GeorgeSkip the underscore.|||I knew it was something that simple!!
Cheers keffenils :)|||I appear to have slipped a cog or two here. Can someone explain what georgev meant based on:DECLARE @.c VARCHAR(50)

SELECT @.c = 'This is a test'

SELECT Len(@.c), DataLength(@.c), @.c AS '12345678901234567890'-PatP|||DECLARE @.c VARCHAR(50)

SELECT @.c ='This is a test '

SELECTLen(@.c),DataLength(@.c), @.c AS'12345678901234567890'

:)|||Ah ha! I keep thinking like SQL Server, so trailing whitespace isn't really there in my mind... This was actually a user-required concession to make CHAR() columns function the way that microcomputer programmers expected them to, long before Microsoft inherited the product.

-PatP|||The trailing spaces are maintained in SQL Server CHAR columns. It is the LEN function that ignores them when returning a value.|||The trailing spaces are maintained in SQL Server CHAR columns. It is the LEN function that ignores them when returning a value.That's what I thought I said.

Somewhere prior to 4.0, spaces were left "as is" by functions, including trailing spaces. By the time that 4.21 was released, all of the string functions were supposed to trim trailing spaces before processing, and convert any string results back into the CHAR() type if that is what the function had originally been passed.

-PatP

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?

THANKSHi

There is no space function but you can compare directly or something like

RTRIM(<fld>) = ''

RTRIM can be affected by the version or compatibility level of SQL Server
you are using.

The SPACE(n) function will create a string of n spaces.

For all available string functions check out books online or
http://msdn.microsoft.com/library/d..._fa-fz_7oqb.asp

John

"Killer" <roninkaiser@.tiscali.it> wrote in message
news:hFBxc.99850$Qc.3858785@.twister1.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS|||"Killer" <roninkaiser@.tiscali.it> wrote in message news:<hFBxc.99850$Qc.3858785@.twister1.libero.it>...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS

It looks like you got an answer in another group - please do not post
to multiple groups independently.

Simon

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKS
To substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
David Portas
SQL Server MVP
|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKSTo substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
David Portas
SQL Server MVP
--|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKSTo substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
--
David Portas
SQL Server MVP
--|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>