CoolSQL

Find SQL Server database and log file size with stored procedure

admin — Thu, 04 Sep 2008 08:14:00 GMT

sp_SDS is a stored procedure in the master database on CITRMANSQL2. It can be used to calculate SQL database and log file size.. It can take 4 parameters breifly described below.

Variable	Description
@TargetDatabase	The database we want to size
@Level	How detailed the report should be, either at the database level or at the individual database file level
@UpdateUsage	The default value of 0 means we do not wish to run "DBCC UPDATEUSAGE"
@Unit	indicates what measurement the report should be in, namely KB, MB or GB. If it's not specified, the measurement used is megabytes.

Typical scenario for simple useage with default parameters

USE master
EXEC dbo.sp_SDS

The first column "Weight (%)" calculates the percentage of total database size that a given database takes. For example, AdventureWorks is 469.94 MB in total, divided by the grand total of 20,404.51 MB, which is 0.023 (i.e., 2.3 %). From the report, it's easy to tell that the majority of database space is taken by DBAReports (approximately 77 %). Data in this column gives DBAs a rough database picture at a glance. The remaining value-related columns are organized in a formula-like structure.

See also: Find size of SQL Server tables and other objects with stored procedure to calculate SQL server table and other object space

See also full instructions available in downloads section: http://license.citruslime.com/cs/files/folders/sample_files/default.aspx

Find size of SQL Server tables and other objects with stored procedure.

admin — Thu, 04 Sep 2008 07:23:00 GMT

sp_SOS is a stored procedure in the master database on CITRMANSQL2. It can be used to calculate SQL server object space. It can take 8 parameters breifly described below.

Variable	Data type	Nullable	Default	Default implication
@DbName	sysname	Yes	NULL	Current database
@SchemaName	sysname	Yes	NULL	All schemas
@ObjectName	sysname	Yes	%	Including all objects in "LIKE" clause
@TopClause	nvarchar(20)	Yes	NULL	All objects. Can be "TOP N" or "TOP N PERCENT"
@ObjectType	nvarchar(50)	Yes	NULL	All objects that can be sized. Valid values are S(system), U(user), V(indexed view), SQ(service broker queue), IT(internal table) or any combination of them
@ShowInternalTable	nvarchar(3)	Yes	NULL	Includes internal table. The Parent excludes it in size
@OrderBy	nvarchar(100)	Yes	NULL	By object name, can be any size related column. Valid short terms are N(name), R(row), T(total), U(used), I(index), D(data), F(free or unused) and Y(type)
@UpdateUsage	bit	Yes	0	Do not run "DBCC UPDATEUSAGE"

Typical scenario for simple useage

@DbName is the database name where you want to find object space in SQL Server. If it's not supplied, it will use the current database

USE WBSC

EXEC dbo.sp_SOS

Type =S(system), U(user), V(indexed view), SQ(service broker queue), IT(internal table)

The formula for space is "Total(MB) - Unused(MB) == Used(MB) = Index(MB) + Data(MB)", i.e., the used space is the simultaneous result of total minus unused as well as index plus data

See also: Find size of SQL Server tables and other objects with stored procedure to find SQL database and log file size

See also full instructions available in downloads section: http://license.citruslime.com/cs/files/folders/sample_files/default.aspx

Batch Set the CitrusStore Detail Link

admin — Fri, 20 Jun 2008 13:47:00 GMT

update products set detaillink='../detail.aspx?ID=' + CAST (products.uid as nvarchar)

Dynamic Search Conditions in T-SQL

admin — Mon, 18 Feb 2008 22:18:00 GMT

This is a great article on the best way to build store procedures when you have a requirement for variable clauses on your where statements (e.g. say search for an order by name or order date). Its pretty damn good!

Dynamic Search Conditions in T-SQL

Neil

Batch Detach Databases

admin — Fri, 15 Feb 2008 10:53:00 GMT

Run this script and send the results to a text file. The resulting text file is a script to detach every database on the server.

--Detaching
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''master'', ''msdb'', ''tempdb'')
BEGIN
SELECT ''EXEC sp_detach_db ''''?'''',''''true''''''
END'