30 June 2014

Turn off Visual Studio 2013 Preview of files

Visual studio 2013 previews the file content when it is selected.
The feature can be very annoying and luckily it can be disabled.

To disable the feature, in the visual studio menu, select Tools -> Options -> Environment -> Tabs
Uncheck all the Preview tab checkboxes.

06 June 2014

Fix SSDT error" Could not load type Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption"

The SQL Server Data Tools for SQL Server 2014 (SSDT) are now available for Visual Studio 2012 and 2013.
The SQL Server Data Tools - Business Intelligence for Visual Studio 2013 (SSDT-BI) are also available.
I installed both and after a while the schema compare started failing with the error:

Error 6 Could not load type 'Microsoft.SqlServer.TransactSql.ScriptDom.OnOffStatisticsOption' from assembly 'Microsoft.SqlServer.TransactSql.ScriptDom, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

The solution that worked for me was to uninstall some components of SSDT and then repair the installation.
The detailed steps are:
1) Open Control Panel and then Programs and Features
2) Uninstall the following items:
    Microsoft SQL Server 2014 Management Objects version 12.0.2299.1
    Microsoft SQL Server 2014 Management Objects (x64) version 12.0.2299.1
    Microsoft SQL Server 2014 Transact-SQL ScriptDom version 12.0.2299.1
3) Select Microsoft SQL Server Data Tools 2013, right click to select Change

press the repair button


Note that this solution fixes the SSDT but may have side effect on the SSDT-BI.

02 June 2014

Undocumented sp_MSforeachdb: Iterate through each database

The stored procedure sp_MSforeachdb is used to iterate through each database that exists in SQL Server, including the system databases.

The stored procedure is undocumented and can be removed at any time, so use it carefully.

It is used to run a command over a set of databases in a server.
The stored procedure receives a parameter with the command to execute. The ? is used as a placeholder to identify the current database name listed by the stored procedure.

Some common scenarios where it can be used are:

1. Print all the database names, excluding the master,tempdb, model and msdb:
EXEC sp_msforeachdb 
"IF '?' NOT IN ('master','tempdb', 'model', 'msdb')   
BEGIN
        PRINT '?'
END"


2. Show the size of all the database
EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'


3. Check the integrity of all objects in the database
sp_MSforeachdb 'DBCC CHECKDB(?)' 


4. Retrieve database physical files information excluding the master,tempdb, model and msdb:
EXEC sp_msforeachdb 
'IF ''?'' NOT IN (''master'',''tempdb'', ''model'', ''msdb'')   
BEGIN
        SELECT name,physical_name,state,size FROM [?].sys.database_files
END'


5. Retrieve database physical files information excluding the master,tempdb, model and msdb to a table:

DECLARE @DbSize TABLE
    (
      mame NVARCHAR(50),
      physical_name NVARCHAR(500),
      size INT,
      growth INT,
   is_percent_growth BIT,
   type_desc NVARCHAR(10)
    )

INSERT  INTO @DbSize
EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''master'', ''tempDB'',''model'',''msdb'')
BEGIN
       SELECT name,physical_name,size, growth, is_percent_growth, type_desc
       FROM ?.sys.database_files
END'

SELECT * FROM @DbSize