Storing your SQL Server Database as Text

Have you ever had trouble moving a database from one server to another?  Ever wanted to store the database in source control?  Storing the database as text (a SQL Server Script) enables plain text tools to operate on your database.

Jeff McWherter from Gravity Works showed me how to easily store a SQL Server Database as Text:

1. In SQL Server Management Studio right click on a database
2. However over Tasks
3. Select Generate Scripts
4. Click 'Set Scripting Options' to skip the first two steps.
5. Click the 'Advanced' button
6. For the last option in general change 'Types of data to script' from schema only to 'Schema and data'
7. Click OK to close the Advanced dialog
8. Select a location to save the script to
9. Click Next to go to summary.
10. Click Next for SQL Server Management Studio to generate the script.
11. Click Finish and you've got your text!
    
12. When executing this script
1. Ensure the Filename in the CREATE DATABASE Statement is correct when executing this script. (Or you can comment out everything after CREATE DATABASE $YOURDBNAME$ until the first GO to have SQL Server use defaults)
2. Since this works at the SQL Server level, you will get errors when the script attempts to create users that don't exist on the target server and then errors when it authorizes that user.

P.S. See my post SQL Server Management Studio Generating Change Scripts if you want to use SQL Server Management Studio to graphically modify the database but then script the change.

Windows and Mac are Dodo Birds

18 days ago I received a CR-48 and have used it as my exclusive computer outside of work.

This machine is perfect because :

1. It is built for the internet.
2. It is resilient.
3. It is powerful.
4. It is convenient.

Chrome OS is built for the internet.  When I'm online I am consuming information and communicating.  Neither of these tasks require the power and complexity of Windows or a Mac operating systems.  Chrome OS gets me online almost instantly and gets out of my way so I can use the web.

Chome OS is resilient.  Suffering data losses in 2001 and 2005 I moved all my important files to Google docs and Windows Skydrive.  Since moving to the cloud I have not lost a bit, and have not cared which operating system I'm on for my personal computing needs.  Chrome OS takes this a step further putting my files and applications in the cloud.  Now if my machine dies I won't waste time reconfiguring my machine the way I want it.

Chrome OS is powerful.  I was surprised I am able to take and edit photos on my CR-48, make phone calls, do online video conferencing with my son, and edit code.  Prior to receiving the CR-48 I assumed some applications didn't make sense as web apps, but now my thinking is flipped.  All apps make sense as web apps and they will all be web apps.  It is just a matter of time.

Chrome OS is convenient.  It is light, it is fast, it is portable, it is quiet, the battery lasts for 8 hours, it is secure, it is hardy.

When Windows Longhorn was announced waaaaaaaay back in the day I was excited about features it was going to deliver like WinFS.  When Windows 7 launched I didn't care about any of the features.  The world has changed.  I don't care about desktop features anymore.  None of them make my life easier and more convenient.  With Windows 8 on the horizon, I can't think of a single desktop feature I want.  Today's compelling use cases are cloud based.  I started using Mac this year.  Which features was I excited about?  Syncing with Google Calendar, syncing with Google Contacts, and storing my data in the cloud.  Chrome OS leapfrogged these operating systems and they will need to catch up or go the way of the dodo bird.

Adding Cascading Deletes

I needed to add delete functionality for an entity in one of our projects.  Unfortunately this table had 30 other tables referencing it.

This SQL generated the correct code for me faster than I could blink:

SELECT 'alter table [' + FK.TABLE_NAME + '] drop constraint [' + C.CONSTRAINT_NAME +']; ' +
'ALTER TABLE [' + FK.TABLE_NAME + 
      '] WITH CHECK ADD CONSTRAINT [' +  C.CONSTRAINT_NAME + 
      '] FOREIGN KEY([' + CU.COLUMN_NAME + 
      ']) REFERENCES [' + PK.TABLE_NAME + ']([' +
  PT.COLUMN_NAME + ']) ON DELETE CASCADE; '
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
where CU.COLUMN_NAME = 'yourColumnName'