Find databases without any owners

SELECT @@SERVERNAME AS ServerName
, [name] as DBName
, SUSER_SNAME(sid) as CurrentDBOwner
FROM sysdatabases
WHERE SUSER_SNAME(sid) is null
OR SUSER_SNAME(sid) = ''

Find where DB Collation is different to Server Collation

SELECT @@SERVERNAME AS ServerName
, [name] as DBName
, SERVERPROPERTY('Collation') AS SvrCollation
, DATABASEPROPERTYEX([name],'Collation') AS DBCollation
FROM sysdatabases
WHERE DATABASEPROPERTYEX([name],'Collation') <> SERVERPROPERTY('Collation')

I found a blog very interesting on www.sqlskills.com which talks about migrating databases among various versions of SQL Server. It’s very common these days for an organisation to run multiple versions of SQL Server to lower down costs on licences. Moving databases to lower versions can be a painful task if any version specific functionality or features are not supported.

Thanks to Microsoft to provide a DMV ‘sys.dm_db_persisted_sku_features ‘ to tell features belonging to specific version.

Source: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Does-my-database-contain-Enterprise-only-features.aspx

Find SQL Server instances on local network

on command prompt: run

c:\ > osql -L


another way is to run from powershell, open "powershell ise" and run as administrator

now run command

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

it will give information as following

ServerName InstanceName IsClustered Version
---------- ------------ ----------- -------

Count of each table in database

the query below will generate sql statements to count number of rows in each table on the database.

SELECT 'select '''+ name + ''', count(*) from [' + name + '] union'
FROM Sysobjects
WHERE Type='U'
ORDER BY 1;

Instant Initialisation

In Windows 2008
gpedit.msc --> computer configuration --> windows settings --> security settings --> local policies --> user rights assignments --> "lock pages in memory"
give sql server service account here.

align primary disk partitions

How to set primary partition of disk drives on a server
cmd --> diskpart
list disk
select disk
create partition primary align = 1024

now goto computer management --> storage
format disk with specified block (64k for compellent and 4k for ramsan)
assign drive letter and label the drive

Note: if any error message comes as like disk is write protected, then
goto disk management --> brign it online --> initialise disk