Drop All Tables in a Database

So I recently had to delete all tables in a database based on schema name. Here is the solution I came up with:

DECLARE @tblName VARCHAR(200) ;

 

DECLARE tbl CURSOR

FOR

SELECT SCHEMA_NAME(schema_id) + ‘.’ + name AS tableName

FROM sys.tables

WHERE schema_id IN (SCHEMA_ID(‘admdb’), SCHEMA_ID(<Your Schema Here>), SCHEMA_ID(<Your Schema Here>)) ;

 

OPEN tbl ;

 

FETCH NEXT FROM tbl INTO @tblName ;

WHILE @@FETCH_STATUS=0

BEGIN

EXEC(‘DROP TABLE ‘ + @tblName) ;

 

FETCH NEXT FROM tbl INTO @tblName ;

END

 

CLOSE tbl ;

DEALLOCATE tbl ;

 

A couple things worth mentioning is that I set the table name to 200 character varchar. Of course this script can do some damage, please, please, please make sure you don’t need anything in any of the tables before running this!

By default, drop table does not accept a variable but if you turn it into a dynamic query (wrapping it in an execute function) works perfectly.

IE9 & Firefox 4 – Facebook & Flash Issues Continued

Are you having problems with Facebook games on Facebook using IE9 or Firefox 4? Another solution…

So I figured something out recently, if you need the hardware acceleration turned on (I have to admit it is a nice thing to have) in your web browser, the problem might be related to your graphics driver. Most laptops today use the Intel graphics drivers. Go to http://downloadcenter.intel.com/ to download the latest drivers (Microsoft’s Windows Update is a little behind on this) then follow the on-screen installation steps. If you are unsure which drivers you have on your system they have a utility that can help you, simply go to http://www.intel.com/p/en_US/support/detect.

If this is not the case for you I recommend going to my previous post, which is still very useful if you are not using an Intel chipset.

Subscribe to this blog if you like this tip.

Fun With Images and Photographs – Photosynth

This is very interesting and compelling. Anyone have an other great ideas for processing of images? What are your plans for images? I would love to hear them as I am planning my own project similar to this one but to a different end and for a different purpose.

More on SQL Server Denali!

This guy has a great blog article on high availability on the latest version of SQL Server. I highly recommend reading it. Unlike previous versions, you can query all the nodes and you aren’t limited to a two node configuration like before! I have another article, a very terse one, here: http://wp.me/p175D9-3o

SQL Server Denali: HADRON ROCKS. | Brent Ozar – Too Much Information | Brent Ozar – Too Much Information.

Hotmail / Live POP3 Settings

In case you are like me, carrying around a smart phone;  I would like to receive email from my ancient Hotmail account. After some searching I found the info here: http://forum.xda-developers.com/showthread.php?t=517354 which was posted by dubstar_04.

K9Mail is a little more complex to setup than the default email client for Android but is far superior when it comes to speed and ease of maintenance.

In case the link gets broken here is what he posted:

It seems that this is a little known fact that msn / Hotmail pop3 access can be gained using these settings. I find that K9 Mail works better than the standard email app. Incoming: Username: blahdeblah@hotmail.co.uk Password: your password POP server: pop3.live.com Port: 995 Security type: SSL (always) Outgoing: SMTP server: smtp.live.com Port: 25 / 587 Security type: TLS (always) Username: blahdeblah@hotmail.co.uk Password: your password When i first set an account up it brings back some blank mail, which i delete after that it works fine. Regards, Dubstar_04

If you are reading this, chances are you have a Windows Live or Hotmail account. If that is the case, I recommend another one of my articles to get the most out your Skydrive.

http://wp.me/p175D9-6V

AddThis For Firefox Extension

So, I found this great Firefox Extension for sharing sites called AddThis. By default it is setup with just about every sharing site but excludes WordPress. Adding it is simple, just click on the plus sign and select WordPress. It will ask you for your WordPress account and that is it!

Give it a try, you might like it!

**Update**

This topic has been getting quite a response, I am going to be posting more detail very soon! Subscribe and keep up to date on the latest: https://refactoringself.wordpress.com/feed

SQL – Turning Several Result Sets into a Single Result Set

In case you need to get several result sets through a single result set, for whatever reason. In my case I have program that works on one result set at a time that needed some aggregate data displayed at the end of the report. Here is what you do:

Create a temporary table to store the table with the most fields in your result set. Everything will use this table for its data. Be sure create a primary key using an int identity(1,1). Without creating a primary key the data will not be stored in any order.

Order the data the way you need it to display and insert it into the temporary table. Then add following result sets to this table.

Note: Data types are important in getting this to work and character/string based data types tend to work the best.

Computers Blogs
Computers

Creating Fixed Width / Ragged Right Files Through SQL

Here is a great tip I thought of on-the-fly. Say you need to make a fixed width file for something that needs to get out right away. Here is what you do: convert to char!

Concatenate all the fields together and converting all of the data to the char data type will allow you to save the results in a perfect fixed width / ragged right format.

Simple and fast!

Groupwise Rules Not Working

So this is a great one I discovered. If your rules are not working, delete the criteria for the new rule you added and TYPE it back in. It turns out that if you copy and paste from an email message/subject/to/from there is potential that it will not work. I think it is because it uses two kinds of encoding.

I wasted time on this so you don’t have to 🙂

IT Jobs – How to keep yours.

I just saw that the number of back-office IT jobs in both Europe and North America is supposed to go from 4 million today, down to 2 million in 2014. Lets face it, we are an eclectic gathering of eccentric people on many ends of the spectrum.

And yes the jobs are going places where the incomes can be a lot less. Five programmers must be better than one really good one, right?

So here is what I am recommending to stay afloat through this temporary rough patch (I give it a decade but no more than that). I have given this advice to some people and it has worked for them.

1. Never stop improving your skills. This is very, very crucial. Why would someone want to keep a Commodore 64 for their office computer? Learning will not just help you, you need use it in your current job. Now if you are Java programmer, you don’t want to learn C# because then other members of your team will not be able to follow your work but learn different libraries within Java.

2. Help others. The people that hoard their knowledge don’t make it very far and certainly will not keep their job. The only thing they have going for them is a very small area of expertise that others can easily learn or work around. If you share your knowledge people will need you for your sage advice and for your technical ability.

3. Be active in the community. Not just in your company but elsewhere (i.e. user groups, forums, conferences, blogs). From a networking perspective alone, there is much to gain from learning from peers. Learning things from people with different careers can also be very useful!

4. Become an expert. You need to become an expert in an area where no one in your group has much knowledge.

Of course, no job is bulletproof but this will make you last longer than most people in your organization. With the steps I provided here you should have no problem getting a job with another company.