Windows Azure Database dimensions in Mb

If you use a Windows Azure database, you know that you can't easily know its dimension (in term of used space) by the Sql Management Studio, you have to use the Azure Portal. But the portal is not update in real time... So, if you wanna know the database used space, you can use this query:

/*Database dimension in MB*/
SELECT SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats

Instead, if you wanna know the dimension in MB for each table in the Database, use the query below:

/* Tables Dimensions in MB */
SELECT sys.objects.name AS 'Table Name', SUM(reserved_page_count) * 8.0 / 1024 AS 'Mb'
FROM sys.dm_db_partition_stats
INNER JOIN sys.objects ON sys.dm_db_partition_stats.object_id = sys.objects.object_id
WHERE sys.objects.type = 'U'
GROUP BY sys.objects.name

Free technical e-book on the Microsoft platform

While a lot of information on application development is available on the internet and in books, that information is becoming harder to digest. If you do not enjoy reading several 500+ page books, or scouring the web for relevant blog posts and articles, you have come to the right place.

Syncfusion publishes the Succinctly series concise technical books that target developers working on the Microsoft platform. Each book is around 100 pages and is guaranteed to enlighten you on the topic of interest. Download a copy, get a cup of your favorite beverage, and enjoy!

Here's the link: http://www.syncfusion.com/resources/techportal/ebooks

Drop constraint for Default Value

Assign a default value to a column via  T-SQL scripts is really simple. Its deletion, instead, isn't so simple 'cause of the variable constraint name (in a multi-server environment or on different environments it will never have the same name).

This script retrieve the constraint name and drop it.


DECLARE @name nvarchar(100)

SELECT @name =  name FROM sys.objects WHERE type = 'D' AND object_id IN
(
SELECT default_object_id
FROM [sys].[all_columns]
WHERE name = 'column_name'
AND object_id =
(
SELECT  object_id FROM sys.objects WHERE type = 'U' AND name = 'table_name'
)
)

EXEC ('ALTER TABLE table_name DROP CONSTRAINT ' + @name)

SqlServer Title Case

Some time ago, I've published a post (HERE) where it was written hot to capitalize strings in SQL Server.

Now I want to show how to capitalize every word in a string.

This topic is more complex, it's necessary to create a User Defined Function that does the work for us:

CREATE FUNCTION dbo.CapitalizeEveryWord(@input NVARCHAR(4000)) RETURNS NVARCHAR(4000)
AS 
BEGIN
DECLARE @position INT
WHILE IsNull(@position,Len(@input)) > 1
SELECT @input = Stuff(@input,IsNull(@position,1),1,upper(substring(@input,IsNull(@position,1),1))),
@position = charindex(' ',@input,IsNull(@position,1)) + 1
RETURN (@input)
END


It's done! Now just invoke that function:

SELECT dbo.CapitalizeEveryWord (Lower(ColumName)) FROM TableName

Export from SQL Server to Excel

Is it possible to export a database table or a SQL query output directly in Excel? Maybe using a stored procedure or with a SQL command? The answer is: YES!

To start, we have to create a Stored Procedure wich does the work. This SP reads table columns and data and creates an excel file with all data and also with columns.


CREATE PROCEDURE proc_generate_excel_with_columns
(
@db_name varchar (100),
@table_name varchar (100),
@file_name varchar (100)
)
AS

--Generate column names as a recordset
DECLARE @columns varchar(8000), @sql varchar (8000), @data_file varchar (100)
SELECT 
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
FROM 
information_schema.columns
WHERE 
table_name=@table_name
SELECT @columns=''''''+replace( replace (@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
SELECT @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
SET @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c -T'''
EXEC(@sql)

--Generate data in the dummy file
SET @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c -T'''
EXEC(@sql)

--Copy dummy file to passed EXCEL file
SET @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
EXEC(@sql)

--Delete dummy file 
SET @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
EXEC(@sql)


Then we have to invoke the SP passing it the Database name, the table* name and the excel file path (It isn't necessary to create it in advance).

EXEC proc_generate_excel_with_columns 'DB_NAME', 'TABLE_NAME','FILE_PATH'

WARNING:
In most systems, some system commands used in the SP are disabled by default. To enable them, use following commands instead of the one before:


EXEC master.dbo.sp_configure 'show advanced options', 1


RECONFIGURE


EXEC master.dbo.sp_configure 'xp_cmdshell', 1


RECONFIGURE

EXEC proc_generate_excel_with_columns 'DB_NAME', 'TABLE_NAME','FILE_PATH'

EXEC master.dbo.sp_configure 'xp_cmdshell', 0


RECONFIGURE

*TIP: We can use also a View or a Temporary Table instead of "TABLE_NAME"

SQL Server 2008 Log File Shrink for all databases on a server

After my previous post (24 june 2009), in which I proposed a script to Shrink log Files for all DB in a SQL Server 2005 machine, now you've got a script to do the same thing on SQL Server 2008 servera:

EXECUTE sp_msforeachdb

'USE ?;

Alter Database ? Set Recovery Simple;

Alter Database ? Set Recovery Full;

DECLARE @LogLogicalName nvarchar(100);

SELECT @LogLogicalName = file_name(2);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);

dbcc shrinkfile(@LogLogicalName, 1);'

Capitalize string in Sql Server

There is a very simple way to capitalize a string in Sql Server (T-SQL)

If your need is just in output, use this:

SELECT UPPER(LEFT(ColumnName,1)) + LOWER(SUBSTRING(ColumnName,2,LEN(ColumnName))) FROM TableName


If instead you need to modify and save values into db:

UPDATE ColumnName SET ColumnName=UPPER(LEFT(ColumnName,1)) + LOWER(SUBSTRING(ColumnName,2,LEN(ColumnName)))

SQL Server Log File Shrink for all databases on a server

If you use SQL Server, you certainly now that the grow up of transacion log file (.ldf file) is a serious problem.

The faster method to resize the transaction log is to do a shrink.

But if there are N databases on a server, a DBA has to repeat the operation for N times. So, I've created this T-Sql script with that you can automatically execute this operation for all databases on a server.

EXECUTE sp_msforeachdb

'USE ?;

DUMP TRANSACTION ? WITH NO_LOG;

DECLARE @LogLogicalName nvarchar(100);

SELECT @LogLogicalName = file_name(2);

DBCC SHRINKFILE(@LogLogicalName, 100);'

sp_msforeachdb: an undocumented microsoft stored procedure that allow to execute T-SQL code on each database on a DB server.

?: the database name given from stored procedure.

file_name(2): a function that return the logical name of db transaction log file.

UPDATE: script for SQL Server 2008

SQL Server 2008 Row Constructors

SQL Server 2008 Row Constructors

SQL Server 2008 come with “Row Constructor”. It helps to write more compact code in less time.
With the use of Row Constructors its possible to insert some rows in a table using a single INSERT instruction:

INSERT INTO dbo.Customers(custid, companyname, phone, address)
VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1')
,(2, 'cust 2', '(222) 222-2222', 'address 2')
,(3, 'cust 3', '(333) 333-3333', 'address 3')
,(4, 'cust 4', '(444) 444-4444', 'address 4')
,(5, 'cust 5', '(555) 555-5555', 'address 5');



In aggiunta a questa possibilità, now VALUES clause also works with other commands. In simple words, its possible to rewrite some SELECt with UNION operator as a single SELECT…VALUES:

SELECT *
FROM
(VALUES
(1, 'cust 1', '(111) 111-1111', 'address 1')
,(2, 'cust 2', '(222) 222-2222', 'address 2')
,(3, 'cust 3', '(333) 333-3333', 'address 3')
,(4, 'cust 4', '(444) 444-4444', 'address 4')
,(5, 'cust 5', '(555) 555-5555', 'address 5')
) AS C(custid, companyname, phone, address);

Read a single value from a query with VB.Net

Sometimes it's necessary to read single value from a table (or a query). Here's the code:

Imports System.Data.Oledb

Dim ConnString As String = "connection string"

' open connection

Dim Conn As New Data.OleDb.OleDbConnection(ConnString)

Cn.Open()

' request execution

Dim SQL As String = "SELECT Data FROM Table WHERE ID = 'aaaa';"

Dim command As New Data.OleDb.OleDbCommand(Sql, Conn)

Dim ID As Integer = command.ExecuteScalar()

'close connection

Cn.Close()

Assign a variable from a Select result with SQL Server

Sometimes will be useful saving a Select query result in a variable, perhaps to reuse it inside another query.
The command to do that is very simple:

DECLARE @variable AS datatype

SELECT @variable = Filed FROM Table WHERE Condition

We only have to comply with certain conditions:

• datatype must be of the same datatype of Field
• if the query return more than 1 row, the variable contains the last row value
• is not possible to use TOP clause
  

Update from Select with SQLServer

Suppose to be in this situation: we have to update some database records starting from query result. There are 2 solutions: we can manual update datas (it will be a problem with thousands of records) or we can use a T-Sql script to do exatcly what we want. Then only condition is that the starting query must have a field on wich we can make a join on.
The script code is below:

UPDATE T
    SET T.Field1a = Q.Field2a,
    T.Fieldb = Q.Field2b
FROM
    (SELECT FieldID, Field2a, Field2b
    FROM Table WHERE Condition) Q
INNER JOIN TableToUpdate T
    ON T.FieldID = Q.FieldID


Where T is the "Table-To-Update" alias and Q is the Select query alias.
The Join clause do the update exactly like we do an "Update ... Where".

String replacement with Tex or nText fields

Problem: How can I replace a string with another one in a field of Text or nText datatype?

Considerations: If the field was of VarChar or nVarChar type, there is no problem, 'cause in SqlServer exists a function (like in T-Sql) that do it in native mode: the "Replace" function. But this function doesn't work on Text and nText fields. So, we have to use a script builded with another function: UpdateText

Solution:
declare @OldText varchar(1000)
set @OltText= 'Old'

declare curs cursor local fast_forward
for
select
    rowid,
    textptr(Field),
    charindex(@OldText, Field)-1
from
    Table
where
    Field
like
    '%' + @OldText +'%'


declare @NewText varchar(1000)
set @NewText= 'New'

declare @txtlen int
set @txtlen = len(@OldText)


declare @ptr binary(16)
declare @pos int
declare @id int


open curs

fetch next from curs into @id, @ptr, @pos

while @@fetch_status = 0
begin
    updatetext Table.Field @ptr @pos @txtlen @NewText

    fetch next from curs into @id, @ptr, @pos
end

close curs
deallocate curs

Notes:
Table is the reference table
Field is the field where the text will be replaced
rowid is the table id (in this example it's of int type, so the @id variable are set like int, for other datatypes change the declaration of @id variable)