Remove Identity Constraint from Column (TSQL)

There's no way to remove an Identity Constraint from a column in a table, except through adding a new column, reassigning the Identity Column data to that new column then recreating the Identity Column (by drop and add) then reassigning back the data to it.

In the following code, I have a PrimaryKey ID that is also set as an Identity Column. What I want to do is to remove the Identity property by creating a new column name 'tempID' then temporarily storing the ID data into it while I recreate ID column so as to remove the Identity property.


ALTER TABLE [TableName]
ADD tempID int NULL
GO
UPDATE [TableName]
SET tempID=[IdentityColumnName]
GO

-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints

GO
ALTER TABLE [TableName]
DROP CONSTRAINT PK_[TableName]
GO
ALTER TABLE [TableName]
DROP COLUMN [IdentityColumnName]
GO
ALTER TABLE [TableName]
ADD [IdentityColumnName] int NULL

GO
UPDATE [TableName]
SET [IdentityColumnName]=tempID

GO
ALTER TABLE [TableName]
ALTER COLUMN [IdentityColumnName] int NOT NULL

GO
ALTER TABLE [TableName]
ADD CONSTRAINT PK_[TableName] PRIMARY KEY CLUSTERED ([IdentityColumnName]) ON [PRIMARY]
--or ADD CONSTRAINT [PrimaryKeyConstraintName]

GO

-- drop all foreign key constraints related to a table
-- see TSQL Script to drop all constraints

GO
ALTER TABLE [TableName]
DROP COLUMN tempID
GO

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.