A freeware and open source SDF file reader

A good tool for browsing and managing Microsoft SQL Server Compact database files:

SQL Server Compact Toolbox

Both Visual Studio add-in and standalone application available.

SQL Server Express: Batch file to check if current user is sysadmin

issysadmin.bat

@echo off

sqlcmd -S (local)\sqlexpress -Q ^
"if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

SQL Server Express: Batch file to give user sysadmin rights

sysadmin.bat

@echo off

set service=MSSQL$SQLEXPRESS

net stop %service%
net start %service% /mSQLCMD

sqlcmd -S (local)\sqlexpress -Q "EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\username', @rolename =N'sysadmin'"

net stop %service%
net start %service%

MS SQL Server 2008: Add domain user to database owner list

1. Start - All Programs - Microsoft SQL Server 2008 R2 - Configuration Tools - SQL Server Configuration Manager
2. Right-click on database, select New Query, paste the following code, replace DOMAIN\user with real value, start script:

EXECUTE master.sys.sp_MSforeachdb ' 
    use [?]; 
    declare @user sysname = ''DOMAIN\user'' 
    if db_name() not in (''master'', ''model'', ''msdb'', ''tempdb'') 
    begin 
        if not exists ( 
            select null 
            from dbo.sysusers 
            where name = @user 
        ) 
        begin 
            print ''Adding '' + @user + '' to '' + db_name(); 
            exec sp_grantdbaccess @user, @user; 
        end; 
    print ''Granting '' + @user + '' access rights to '' + db_name(); 
    exec sp_addrolemember db_owner, @user; 
    end;
'

How to select a random row from a SQL database table

Here is how to select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

For PostgreSQL, change =RAND()= to =RANDOM()=.

For Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Remove =LIMIT 1= (=TOP 1=) to return all rows from a table in a random order.

[Solution] Add a composite primary key with Microsoft SQL Server Management Studio

A primary key of a table is not a single column, but it is a set of columns. Here is a good article about Composite Primary Keys.

How to create a composite primary key with Microsoft SQL GUI:

1. Open Microsoft SQL Server Management Studio.
2. Right-clickon table and select Design.
3. Create the first primary key - right-click on the column and select Set Primary Key item.
4. Right-click again and select Indexes/Keys...
5. Under (General) section, click on Columns line and click button with three dots ...
6. Add new primary key from combobox by clicking on empty line in the Column Name column.
7. Select sort order from combobox in the Sort Order column.
8. Click OK, Close
9. Save changes by selecting File/Save, or pressing Ctrl+S

[Solution] DataTable doesn't contain primary key information

If PrimaryKey collection of System.Data.DataTable object is empty, add the following line to your code (in bold):

SqlConnection sqlConnection = new SqlConnection(
    @"Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=" + databaseName + ";Integrated Security=True");
sqlConnection.Open();

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("SELECT * FROM " + tableName, sqlConnection);
DataSet dataSet = new DataSet();
sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
sqlDataAdapter.Fill(dataSet);
DataTable dataTable = dataSet.Tables[0];
sqlConnection.Close();

foreach (DataColumn primaryKeyColumn in dataTable.PrimaryKey)
{
    Console.WriteLine(primaryKeyColumn.ColumnName);
}

More information: System.Data.DataAdapter.MissingSchemaAction Property