virtualadrian.com

Jun

27

Scripting SQL Server 2005 Stored Procedures to Individual Files

Adrian Random Rants Add you comment

I had an interesting problem. I have to do a compare between our development database server and source control for all stored procedures and if needed sync them. So the way I was going to do it was to script out all stored procs and use WinDiff to compare the folder I dumped to from SQL Server and our Source Control folder. Problem was how do I script like 2000 ( yes two thousand ) stored procedures to individual files !? I am pretty sure that in Enterprise Manager you could just right click the whole selection and say script to files or something of the sort. In 2005 that functionality seems to be missing. Well it’s not… It just got moved. After some Googling ( yeah, that’s a verb now ), I found this blog :

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

This shows how to script the database entirely, triggers, constraints, etc. I just wanted the procs. So what to do?? Well if you look through his tutorial, you don’t want all the objects in the database so where he tells you to check this box:

Proc Gen

Don’t … Leave that blank since you don’t want all the objects.

The next screen will have a whole bunch of properties, I didn’t really touch those… least I don’t think I did. Just hit Next and you will arrive at this screen where you want to select Stored Procedures :

SQL Server Proc Script

Hit Next to go to the following screen where you will see all of your stored procs. I chose Select All since that is what I needed but you can pick and choose here.

SQL Server Proc Generation 3

Once you have chosen which procs to script out you can hit Next. You will arrive at the last screen. This is where you specify a folder to script the stuff out to:

SQL Server Proc Gen 4

On this screen you want to specify that you want to Script to file, you want one File per object, and then the File name or in our case the directory. If you are doing file per object just put in something like C:\SQLDump\ . That will dump all your stored procs to that folder.

That’s it… just get ready to wait if you have a lot of procs. The server I did this on had a few thousand so that took like 5 minutes.

Two responses before the comments get posted

1. I took the screens off of our Dev server so thats why I blanked out some of the information.
2. If this seems painfully obvious to you I am sorry… I searched for how to do this and all I found was a lot of people complaining that they can’t script stuff out anymore and how SQL Server 2005 is a step back.

Anyway, if you want to compare what you scripted out with another source, just use WinDiff to compare directories. I had to use this Bulk Rename Utility to name the scripted files the same as the source files.

PS: Big thanks to Pinal Dave for the initial post on how to script that DB out that got me on the right track!!

Jun

23

What exactly is Google Maps trying to tell me?

Adrian Random Rants Add you comment

I am not sure but if I did this on my motorcycle I wouldn’t be around for long :

Jun

12

Turning Rows Into Columns In SQL Server

Adrian Random Rants Add you comment

So I had a recent need to take a bunch of rows that I select and turn them into columns so I could put a string together for a specific column. What am I talking about? Well say that you have a table called Employees.

SELECT * FROM EMPLOYEES




ID 
FIRST_NAME 
LAST_NAME


-----------
------------------------------------ 
------------------------------------


1
Adrian 
P


2 
Ryan 
S


3
Dan 
W


4 
Bruce
A

(4 row(s) affected)

At this point say that what you want to throw back to your application is :

“Adrian, Ryan, Dan, Bruce are online!”

So you are probably thinking cursor in the stored proc or loop on the application side. That’s where we can use a recursive trick to save ourselves a bit of time and cursor writing. Don’t worry it’s not hard.



DECLARE @EMPLOYEE_LIST VARCHAR(50)
SELECT @EMPLOYEE_LIST = COALESCE(@EMPLOYEE_LIST + ', ','') + FIRST_NAME

FROM EMPLOYEES
SELECT @EMPLOYEE_LIST AS EMPLOYEES_ONLINE





EMPLOYEES_ONLINE 


-------------------------------------------------


Adrian, Ryan, Dan , Bruce

(1 row(s) affected)

That’s it no cursor or special stuff to do. It works because of the way you are assigning @EMPLOYEE_LIST to itself. Kinda like a recursive CTE… only without the CTE I have only done this in SQL Server 2005 so if it does not work in 2000, or 2008 ( although I would assume everything from 2005 should move up ) you may end up with a cursor or some loop.

Note: Watch the result set! I haven’t tried this with a few million rows, but I would assume you may bog down the CPU if you try to do this with a few thousand rows or something. The last thing you want is a few DBAs at your desk giving you the stare LOL

Jun

06

F*CK Wal-Mart

Adrian Random Rants Add you comment

So I was watching this video on You-Tube

The dude is pretty funny so I watch his show. This is outrageous! I know I work for the competition but for crying out loud! How can you sue a brain damaged woman and her family and leave them in the poor house, and still sleep at night?

Message of the day STOP SHOPPING AT WAL-MART! Their products suck, most stores are a pit, and the company is EVIL! Quit giving these MONSTERS money! Here is the link to the article on CNN:

http://www.cnn.com/2008/US/03/25/walmart.insurance.battle/index.html

Jun

05