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 vs stored proc

Is there a benefit to using one over the other?
Both give me the same result. Should I go with the function or the stored
proc?
ALTER FUNCTION f_PhraseCount
(
@.Phrase VARCHAR(512)
)
RETURNS INT
AS
BEGIN
Declare @.PhraseCount INT
SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
RETURN @.PhraseCount
END
or
ALTER PROCEDURE dbo.spr_PhraseCount
(
@.Phrase VARCHAR(512)
)
AS
SELECT COUNT(*) from SearchHistory WHERE Phrase= @.PhraseIT depends on how you wish to use it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mrmagoo" <-> wrote in message news:ef4%23ZWEZGHA.4688@.TK2MSFTNGP04.phx.gbl...d">
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>
>

function versus stored proc

I have to repost this, so I apologize if anyone responded. For some reason
the copy I sent is not being refreshed in my newsreader Inbox.
Is there a benefit to using one over the other?
Both give me the same result. Should I go with the function or the stored
proc?
ALTER FUNCTION f_PhraseCount
(
@.Phrase VARCHAR(512)
)
RETURNS INT
AS
BEGIN
Declare @.PhraseCount INT
SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
RETURN @.PhraseCount
END
or
ALTER PROCEDURE dbo.spr_PhraseCount
(
@.Phrase VARCHAR(512)
)
AS
SELECT COUNT(*) from SearchHistory WHERE Phrase= @.PhraseIt comes down to what kind of interface you want your clients to have to use
in order to get the data from the database. A stored procedure, to me,
"feels" more like a solid interface (from a client perspective) than does a
T-SQL function. Compare:
EXEC dbo.spr_PhraseCount @.Phrase='x'
to:
SELECT dbo.f_PhraseCount('x')
By using the function, you're exposing a little piece of SQL to the client
(i.e., the SELECT statement), whereas the stored procedure completely
encapsulates it. This is a very small issue in this case, but it might
illustrate the direction that you want to take the entire application in
from a data access point of view. Ultimately, you need to decide how much
you want to couple your client (the application tier) to the database.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
>I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Currently they give you the same result, but if you wanted to change things
slightly stored procedures have more flexibility regarding returning
recordsets
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"mrmagoo" <-> wrote in message news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> I have to repost this, so I apologize if anyone responded. For some reason
> the copy I sent is not being refreshed in my newsreader Inbox.
> Is there a benefit to using one over the other?
> Both give me the same result. Should I go with the function or the stored
> proc?
> ALTER FUNCTION f_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> RETURNS INT
> AS
> BEGIN
> Declare @.PhraseCount INT
> SELECT @.PhraseCount = COUNT(*) from SearchHistory WHERE Phrase = @.Phrase
> RETURN @.PhraseCount
> END
>
> or
>
> ALTER PROCEDURE dbo.spr_PhraseCount
> (
> @.Phrase VARCHAR(512)
> )
> AS
> SELECT COUNT(*) from SearchHistory WHERE Phrase= @.Phrase
>|||Thanks Adam for your insightful response.
I had originally posted this but lost the thread in my newsreader. However,
I performed a groups.google.com search and found it. I had received a
response from someone named Tibor Karaszi, a SQL Server MVP, who answered my
exact same post with the very brief and useless reply of "IT depends on how
you wish to use it.". That's all he said, so I thank you for your
elaboration. I have participated in the MS newsgrouips for a long time, and
I wish MS would award the MVP status to people like you who take the time to
give people like me information I can use. Whereas Tibor gave me such
pathetically little information that I can do nothing with, and I wonder why
he even bothered to spend what must have been 10 seconds replying to such a
meaningless question to him (it seems), you have provided me information
that I can use.
Thank you.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> It comes down to what kind of interface you want your clients to have to
use
> in order to get the data from the database. A stored procedure, to me,
> "feels" more like a solid interface (from a client perspective) than does
a
> T-SQL function. Compare:
> EXEC dbo.spr_PhraseCount @.Phrase='x'
> to:
> SELECT dbo.f_PhraseCount('x')
> By using the function, you're exposing a little piece of SQL to the client
> (i.e., the SELECT statement), whereas the stored procedure completely
> encapsulates it. This is a very small issue in this case, but it might
> illustrate the direction that you want to take the entire application in
> from a data access point of view. Ultimately, you need to decide how much
> you want to couple your client (the application tier) to the database.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||Thank you.
"Jack Vamvas" <delete_this_bit_jack@.ciquery.com_delete> wrote in message
news:mbCdnaLFI9MmDtXZRVny1A@.bt.com...
> Currently they give you the same result, but if you wanted to change
things
> slightly stored procedures have more flexibility regarding returning
> recordsets
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "mrmagoo" <-> wrote in message
news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
reason
stored
@.Phrase
>|||I'm sorry to hear that you weren't pleased with Tibor's reply -- he is
generally a great source of knowledge on all things SQL Server related, and
he's helped me many times in these groups and elsewhere.
As for MS awarding MVP to people like me, it does happen -- I've been an MVP
since 2004 ;-)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"mrmagoo" <-> wrote in message
news:%23B59gVWZGHA.4248@.TK2MSFTNGP05.phx.gbl...
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader.
> However,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
> my
> exact same post with the very brief and useless reply of "IT depends on
> how
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time,
> and
> I wish MS would award the MVP status to people like you who take the time
> to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder
> why
> he even bothered to spend what must have been 10 seconds replying to such
> a
> meaningless question to him (it seems), you have provided me information
> that I can use.
> Thank you.
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:uAjzxGRZGHA.1196@.TK2MSFTNGP03.phx.gbl...
> use
> a
> news:e$szWgPZGHA.1200@.TK2MSFTNGP03.phx.gbl...
> reason
> stored
> @.Phrase
>|||mrmagoo wrote:
> Thanks Adam for your insightful response.
> I had originally posted this but lost the thread in my newsreader. However
,
> I performed a groups.google.com search and found it. I had received a
> response from someone named Tibor Karaszi, a SQL Server MVP, who answered
my
> exact same post with the very brief and useless reply of "IT depends on ho
w
> you wish to use it.". That's all he said, so I thank you for your
> elaboration. I have participated in the MS newsgrouips for a long time, an
d
> I wish MS would award the MVP status to people like you who take the time
to
> give people like me information I can use. Whereas Tibor gave me such
> pathetically little information that I can do nothing with, and I wonder w
hy
> he even bothered to spend what must have been 10 seconds replying to such
a
> meaningless question to him (it seems), you have provided me information
> that I can use.
>
There are very few people here who are more helpful than Tibor. I
expect his reply was intended to prompt you for more information.
Please remember that any help you do get is for free. If you want a
guaranteed level of service you'd better go hire someone. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I am aware that people here contribute for "free", however MVPs get MSDN,
don't they?, so contributions are not without remuneration. Non-MVPs are the
only ones who truly work for free, and I doubt that an MVP wannabe would get
to be an MVP by answering posts that way.
Your point is well taken, however. I did not mean to offend Tibor, but I did
intend to point out that the reply was not helpful.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145642545.679167.247350@.t31g2000cwb.googlegroups.com...
> mrmagoo wrote:
However,
answered my
how
and
time to
why
such a
> There are very few people here who are more helpful than Tibor. I
> expect his reply was intended to prompt you for more information.
> Please remember that any help you do get is for free. If you want a
> guaranteed level of service you'd better go hire someone. :-)
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Function to validate e-mail address

does anyone have a funciton or Stored PRoc to validate internet e-mail addresses?

I have a table of e-mail addresses, many of which are junk (the web programmer did not enforce validation on the survey form) and now i have to filter the junk out of the list.

Can any one help with this? Is there a UDF or SP floating out there I could use?These links may give you some ideas:

One that does it in the database

One that does it in C#