How to drop all Database connections (TSQL)

USE master
GO

ALTER DATABASE @dbName
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE @dbName
SET ONLINE

OR simply
Right Click database -> Tasks -> Take database offline

How to check/count Back Links

I wrote about How to Create Back Links previously.

Now let's see how to check for back links. This is useful for blogs, websites, etc.

On the Google search bar simply type
link:

For example
link:thingswelovetohate.blogspot.com
or
link:http://thingswelovetohate.blogspot.com

would result to the same number of back links.

You can determine how many back links a website has on the same window. Right above the search results, and right below the Search text box is a blue (in classic mode) highlighted line that shows how many results were found.

For example
link:www.mylot.com



You will see that it had a total of 142 back links at the time of the search.

Downgrade from SQL 2008 to SQL 2000

I was given the task to downgrade a SQL 2008 to SQL 2000. I had to do it as soon as possible (= now) and I didn't have SQL 2000 in my machine.

Detaching the database from SQL 2008 and attaching to SQL 2000 doesn't work - they tried that already. Export/Import doesn't work also - so I tried my luck on using the simplest way to do the job:

Scripting.

In SQL Server 2008 Management Studio.
Databases -> MyDatabase, right click to Tasks -> Generate Scripts.



In the Script Wizard that pops up.
1. Select the database (MyDatabase/AdventureWorks)
the Check Script all objects in the selected Database.
2. Click Next.
IMPORTANT : Choose SQL 2000 in Script for Server Version

Toggle the following from False to True:
Script Database Create
Script Data
Script Indexes
Script Triggers
3. Click Next.
4. Click Finish.

Of course if you have a humongous database then the scripting the data would mean that you'd have a huge resulting .sql file. You may opt to not script the data alongside the Database itself for convenience, but if you don't have a big database then this is good enough - and pretty convenient.

How to: Inherit Database Role permissions

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'RestrictedRole')
exec sp_addrole 'RestrictedRole', 'dbo';

IF NOT EXISTS (SELECT name FROM sysusers WHERE issqlrole=1 AND name = 'LessRestrictedRole')
exec sp_addrole 'LessRestrictedRole', 'dbo';


GRANT SELECT, INSERT, UPDATE on Table1 to RestrictedRole;
GRANT SELECT on Table2 to RestrictedRole;

EXEC sp_addrolemember 'RestrictedRole', 'LessRestrictedRole';

GRANT INSERT, UPDATE on Table2 to LessRestrictedRole;

The sp_addrolemember makes LessRestrictedRole a member of RestrictedRole, which means that LessRestrictedRole inherits all permissions given to RestrictedRole.

Granting more permissions to LessRestrictedRole would make those additional permissions exclusive to LessRestrictedRole - LessRestrictedRole may Insert and Update rows in Table2 while RestrictedRole may only Select rows.

By virtue of Inhertance, both LessRestrictedRole and RestrictedRole may Select, Insert and Update Table1.

CREATE new schema and ALTER table schema

To create a new Database Schema:
CREATE SCHEMA NewSchema
GO

To create a new Database Schema only if it does not exist:
IF NOT EXISTS (SELECT * FROM sys.schemas
WHERE name ='NewSchema')
EXEC dbo.sp_executesql @statement=N'
CREATE SCHEMA NewSchema';
GO

To change a table's Schema:
ALTER SCHEMA NewSchema TRANSFER OldSchema.TableName;
--default schema is dbo then:
ALTER SCHEMA NewSchema TRANSFER dbo.TableName;

Add DB Role IF NOT EXISTS

Check if the Database Role exists before creating it:

DECLARE @Role varchar(20) = 'UserRole'
IF NOT EXISTS (SELECT name FROM sysusers
WHERE issqlrole=1 AND name = @Role)
EXEC sp_addrole @Role; --EXEC sp_addrole @Role, 'dbo'

(TSQL) Escape Underscore in Like

SELECT * FROM Table WHERE Column LIKE '_a_'

The above code will generate results that has any one character before and after 'a' such as:
mac
cat
sad

SELECT * FROM Table WHERE Column LIKE '%[_]a[_]%'

The above code will generate results such as:
in_a_house
she_is_a_mother
make_a_living