Vote utilisateur: 3 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles inactivesEtoiles inactives

A script for SQL Server to be run as sysadmin or a user that have enought priviledges on all databases to list all tables :


CREATE PROCEDURE [dbo].[sp_get_tables_sizes_all_dbs]


--sqlserver 2005 +
IF (SELECT count(*) FROM tempdb.sys.objects WHERE name = '##TABLESIZES_ALLDB')=1 BEGIN

snapdate datetime,
srv nvarchar(1000),
sv nvarchar(1000),
_dbname nvarchar(1000),
nomTable nvarchar(1000),
"partition_id" bigint,
"partition_number" int,
lignes bigint,
"memory (kB)" bigint,
"data (kB)" bigint,
"indexes (kb)" bigint,
"data_compression" int,
data_compression_desc nvarchar(1000)

EXECUTE master.sys.sp_MSforeachdb
'USE [?];
insert into ##TABLESIZES_ALLDB
select getdate() as snapdate,cast(serverproperty(''MachineName'') as nvarchar(1000)) svr,cast(@@servicename as nvarchar(1000)) sv, ''?'' _dbname, nomTable= object_name(p.object_id),p.partition_id,p.partition_number,
lignes = sum(
When (p.index_id < 2) and (a.type = 1) Then p.rows
Else 0
''memory (kB)'' = cast(ltrim(str(sum(a.total_pages)* 8192 / 1024.,15,0)) as float),
''data (kB)'' = ltrim(str(sum(
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
) * 8192 / 1024.,15,0)),
''indexes (kb)'' = ltrim(str((sum(a.used_pages)-sum(
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END) )* 8192 / 1024.,15,0)),p.data_compression,

from sys.partitions p, sys.allocation_units a ,sys.sysobjects s
where p.partition_id = a.container_id
and p.object_id = and s.type = ''U'' -- User table type (system tables exclusion)
group by p.object_id,p.partition_id,p.partition_number,p.data_compression,p.data_compression_desc
order by 3 desc'


Commentaire (0) Clics: 6933

Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives

Nagios is a great free tool to supervize many many things in IT. SQL Server is one appliation that can be monitored by Nagios. A great plugin check_mssql_health contains many checks from performance counters to space used and connection time (36 checks at the time of writing).

In order to secure checks on databases, you need to create logins, users and roles on every instances and databases you want to monitor. The Consol Labs web site (Nagios pluggins & Addons editor) give you a script to create (and drop if needed) these objects on one MSSQL instance but : 
1 - We found some missings elements on the script
2 - We will give you one method to quickly launch your create script on several MSSQL instances easily using MSSQL Central Management Server or SSMS Registered Groups

Commentaire (0) Clics: 9585
Etoiles inactivesEtoiles inactivesEtoiles inactivesEtoiles inactivesEtoiles inactives

I was glad to see on mssqltips a script to generate column list for table in order to by use in hashbytes function later for table comparison : article with original get_hash_field function

But as often I needed to do more than just concatenate all fields. My need is :

  • insert a symbol between field to avoid (or limit) the "birthday paradox" : avoid to obtain the same final string if you concat "123" and "45" or you concat "12" and "345"
  • replace null field with a dummy string 'µ' for me
  • be able to exclude 2 lists of column for build the list of fields : I wanted all columns of the table except 1 or more fields nor fields in the key that was not needed for me. In the orginal function this list was static

For that I use store procedure instead of function and dynamic sql was mandatory (for the parameters @p_exception_column_list and @p_key_column_list that are strings with quotes) :

Commentaire (0) Clics: 8191
database publication

Vote utilisateur: 5 / 5

Etoiles activesEtoiles activesEtoiles activesEtoiles activesEtoiles actives

Un script de restore database ms sql server avec récupération de la dernière sauvegarde effectuée sur le serveur (ou récupération directe d'une sauvegarde via un chemin).

Ce script est capable de récupérer les différents fichiers de la sauvegarde et de construire une commande de restauration en changeant la destination des fichiers cibles 


Commentaire (2) Clics: 7657