Using TOP To Rank Columns In a Table

Introduction

“Do you know how to write a SQL statement that will select the data from the top 10 columns in a table”?  This took a few moments to register; you want the top 10 columns from a table and not the rows. Ok...Here we go...

Means To an End

Since the TOP clause provides the first set of rows in a column, I knew the only path to this solution would be through the metadata of SQL Server.  My table of choice for this solution will be INFORMATION_SCHEMA.COLUMNS, which holds the key ingredient to ranking columns in a table.  You guessed it, the ordinal position.  Since ordinal position dictates the column order for columns in a table physically, this is where I chose to base my ranking of TOP columns.  Let’s not forget that our developer not only wants the top columns from the table, he also wants the data in these columns

Our Solution

The first piece of the puzzle here is to capture our TOP column names in a form that can be used dynamically later on in the script.  For this task we will simply create a variable to hold the output of the column names captured from INFORMATION_SCHEMA.COLUMNS.  We will also use the ORDER BY statement to rank our columns for the TOP clause.

Step 1:  Build the column list

DECLARE @columnName VARCHAR(4000)

--This seeds the variable for use. 
SET @columnName = ''

SELECT TOP 10 @columnName = @columnName + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION ASC

This statement is a very simple, cursor free solution that will create a comma separated list of column names based on the TOP clause.  You will notice that I have also wrapped brackets around the column name to allow for the inevitable key word or column name with illegal characters in them.  By concatenating the variable @columnName in the SELECT statement, we eliminate the need for cursors here.  Our next step is to stage the @columnName variable for use in a dynamic SQL statement.

Step 2: Prep the comma separated column name list

SUBSTRING(@columnName, 1, DATALENGTH(@columnName) - 2)

We will need to make use of the SUBSTRING function to remove the trailing comma and space from the very last column name in the list.

Step 3: Putting it all together

/***********************************************************************************************
AUTHOR: Tim Parker
DATE: 8/11/2011
PURPOSE:
This script will return the TON N columns for a given table, and then will dynamically
build and execute a SELECT statement using these columns against the table.
***********************************************************************************************/

DECLARE @columnName VARCHAR(4000),
 @dyanamicSQL VARCHAR(8000),
 @tableName VARCHAR(255)

--This seeds the variable for use. 
SET @columnName = ''

--Supply the table name to reference.
SET @tableName = 'MyTableName'

--You must manually set the TOP N columns.
SELECT TOP 10 @columnName = @columnName + '[' + COLUMN_NAME + '], '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND TABLE_SCHEMA = 'DBO'
ORDER BY ORDINAL_POSITION ASC

--You will need to modify the SELECT statement accordingly here. 
SET @dyanamicSQL = 'SELECT ' + SUBSTRING(@columnName, 1, DATALENGTH(@columnName) - 2) + ' FROM ' + @tableName +

EXEC( @dyanamicSQL )

The completed solution allows the user to supply a table name as the parameter, and set the TOP N columns they will need for the SELECT statement.  This completed solution allows the developer to select the data from the top 10 columns in a table.  Since no two situations are the same, consider this solution a good base line for solving other problems that may rely on ranking columns in a table.

Author: Tim Parker

Auditing DDL Changes in SQL Server databases

Even where Source Control isn't being used by developers, it is still possible to automate the process of tracking  the changes being made to a database and put those into Source Control, in order to track what changed and when. You can even get an email alert when it happens. With suitable scripting, you can even do it if you don't have direct access to the live database.  Grant shows how easy this is with SQL Compare.

SQL Server Source control for bandits and anarchists.

A friend recently described to me how he had to give administration privileges to developers on production. I was appalled because I had lived through a similar experience at a ‘dot com’. My ‘dot com’ went even further: they gave ‘sa’ privileges to everyone in the company, including the receptionist, and then encouraged everyone to try things out because “This database stuff isn’t that hard.” Ah, good old ‘outside-the-box’ Harvard Business School thinking. In consequence, it was a constant battle to find out why the production system was going off-line regularly: Once a salesman even dropped the tempdb because “we don’t want temporary objects in our system.” After this experience, I’ve always cracked down on letting anyone into my production servers with too many rights. 

But every day on the forums, I read about people dealing with users that have ‘sa’ privileges on production, developers developing in production, and the poor database professionals who, like my friend, are forced to deal with these situations. Columns, tables and whole databases appear and disappear. Functionality changes and no one knows why. Figuring out how to keep track of the changes in a production system, just to help put out the latest fire, becomes an imperative.

The best way to fix this issue? Put in place a well-documented development and deployment process that limits who has access to production. Unfortunately, not everyone is going to be able to do this. There may even be valid reasons (although I can’t imagine what they might be at the moment) why your company would allow that sort of extremely risky behavior. This means you have to deal with the situation, come what may.
Then there’s the other side of the coin. If you’re managing financial data in a publicly-traded company or you’re working with credit card data, you don’t have that ‘Dodge City on a Saturday night’ feeling in your system. Instead, you’re also dealing with Sarbannes-Oxley compliance where every change to the structure has to be recorded. You have to meet the PCI Section 10.2 of the Payment Card Industry Data Security Standard which reads in part:

10.2 Implement automated audit trails for all system components to reconstruct the following events: 10.2.7 Creation and deletion of system-level objects
Whether you’re dodging the bullets of all ‘sa’ all the time access, or hand-cuffed to a rigid regulatory regime, you need the ability to know what’s changed on your production systems and keep track of those changes over time. There are plenty of ways of monitoring DDL changes. You can set up DDL triggers to output changes to some set of tables. You could set up a task to monitor the default trace and try to capture changes into a table again. You could use Profiler to set up a server-side trace and capture events that show structural changes which output to a file. You might, if you’re feeling terribly smart and brave, use Extended Events. All of these are ‘intrusive’ in that they require actions that make changes on the server and possibly impact performance. Further, while they all give you a list of changes, they are also a long way from allowing you to compare ‘before’ and ‘after’ scripts to see what’s changed, or letting you explore backups to find out retrospectively when a change was made. They can’t help to check immediately that the code in production is exactly the code that is in the proper, labeled, version in source control. I’d like to suggest that Red Gate’s SQL Compare Professional could help you with tasks like this.

Automating SQL Compare

No, I don’t think you should fire up the SQL Compare GUI every morning when you get into work and compare your development and production servers. Let’s face it, you might not have a development server if your development takes place on production (there’s that Saturday night gun fire again). But you don’t have to use the GUI to use SQL Compare. The Professional edition comes with a command line that you can put to work. With the command line you can automate a comparison to capture changes that have been done to your production system. But what do you compare to?

Here, we get some interesting choices. Again, you could compare between your development database and your production database. The problem with this is, your development database is likely to be different from the production database because that’s where development is taking place. New and different objects will be, and should be, all over the place. So that makes it a poor choice to audit changes in your production system. This leaves you with two good options: First, you could use backups. SQL Compare can compare a live database and a backup (or a backup and a backup or… you get the idea). Another option, and frankly, the better one in my opinion, is to compare between your production database and a set of scripts: This is because SQL Compare Pro can use scripts to compare to a live database (and a backup and… I’ll shut up now). This adds one more type of software that you can toss into the mix for a complete solution, source control. And what if you automated all of this using Microsoft’s excellent automation language, PowerShell? Now we’re talking…

[Editor: Why would this be better than using the command line?]

Yes, that was really my editor asking that question. I know it’s because he wants me to tell you that by using PowerShell you get a full scripting language that can handle large numbers of objects using set based operations through the pipe. PowerShell also provides more command flow than what’s possible in a batch command and it’s easier to automate through SQL Agent. There’s more! You can also enable remoting so that you can run these processes on multiple servers at the same time as well as all the other great stuff that’s possible through PowerShell. That’s what he wants me to say, but my short answer is: “Dude! Come on! It’s PowerShell, duh! Of course we’re going to use it.”

Using Source Control and Scripts to Audit Schema Changes

In order to use SQL Compare, you have to have something to compare against (even if it’s just a blank folder). Hopefully, you already have your database in some form of source control as part of your development and deployment processes. If not, SQL Compare provides a mechanism for making it happen. Whether you do or not, this code in source control will need to be stored separately from your development code in order to isolate keeping track of production changes from your development processes. There will be no use of the pretty GUI in any of these examples. Everything will be done through the command line. More specifically, all calls will go through PowerShell invocations of the command lines. You can use any source control system. I’m using SourceGear’s Vault for the examples. There are attachments that use SVN and TFS. First, I want to generate the scripts for my entire database (if you want to do the whole server, all at once, here’s a script for that too):

$DBName = "MovieManagement";
$Server = "grant-red1\gfr1";
$Scripts = "c:\dbscripts";

$Command = "C:\Program Files (x86)\Red Gate\SQL Compare 9\sqlcompare.exe";
[Array]$arguments = "/s1:$Server","/db1:$DBName","/mkscr:$Scripts\$DBName","/q";

& $Command $arguments;

This will create the script for your database in the location specified. The commands for the SQL Compare Pro command line are very straightforward:

• /s1 – That’s the server we are comparing from
• /db1 – That’s the database that is the source
• /mkscr – Creates a script out of all objects in the database
• /q – Makes it “quiet” or less verbose

To get the code from that location into source control, you’d need to use the command line from your source control system. The one for the Vault software I’m using looks like this:

$DBName = "MovieManagement";
$Scripts = "c:\dbscripts";

set-location "C:\Program Files (x86)\SourceGear\Vault Client\";

./vault REMEMBERLOGIN -user admin -password 12345 -host VaultHost -repository MyRepo
./vault.exe add $ $Scripts\$DBName;
./vault.exe commit;
./vault.exe checkin;

If you’re working with lots of servers or lots of databases, you might want to combine these scripts and do a little more automation around getting everything into the system in one pass.. The important point is that I now have a database that is checked into source control. With that, it’s a trivial task to then create an automated script that will nightly compare my database and my scripts in source control.

What I’m going to do is set up a script that will get the latest code from source control. It will then compare this code to the production server. If there are changes in production, first, SQL Compare will generate a report of these changes. Second, an email will be sent to you, alerting you to the issue. Finally, the changes will be applied to source control and labeled with the current date so that you can retrieve them over time and so that any new changes tomorrow will get their own report and email:

## 1. Set Variables
$DBName = "MovieManagement";
$Server = "MyServer\MyInstance";
$Scripts = "C:\dbscripts";
$Date = Get-Date;
$pswd =  ConvertTo-SecureString "12345" -AsPlainText -Force
$Creds = New-Object System.Management.Automation.PSCredential ("login", $pswd)

## 2. A little construct to deal with a shortcoming in Vault
$Date.AddMinutes(-2);

## 3. Getting the latest from Source Control. could get version or label instead
set-location "C:\Program Files (x86)\SourceGear\Vault Client\";
./vault REMEMBERLOGIN -user admin -password 12345 -host VaultHost -repository MyRepo
./vault GET $/$DBName;
./vault CHECKOUT $/$DBName;

## 4. SQL Compare database to source code
set-Location "c:\Program Files (x86)\Red Gate\SQL Compare 9\";
$MyOUtput = ./sqlcompare /s1:$Server /db1:$DBName /scr2:$Scripts\$DBName  /sync /report:$Scripts\schemaDiff.html  /reporttype:Simple /f;

## 5. Determining if there are changes
set-location "C:\Program Files (x86)\SourceGear\Vault Client\";
IF ($MyOUtput[11] -ne "Error: The selected objects are identical or no objects have been selected in")
{
## 6. There are changes, add them all to source control
##These have to be walked & date checked because Vault won’t recognize new files
Get-ChildItem $Scripts\$DBName -Recurse |  where-Object{!$_.PsIsContainer} | where-Object{$_.CreationTime -gt $Date} | foreach-object {./vault ADD -commit ("$" + $_.FullName.Replace($Scripts,"").Replace("\"+$_.Name,"").Replace("\","/")) $_.FullName};

## 7. send an email with the report
Send-MailMessage -From service@red-gate.com -To Someone@red-gate.com -Subject "Schema Changes in database $DBName" -Body "The attached report shows changes that have occurred in the last 24 hours" -Attachments $Scripts\schemadiff.html -SmtpServer mail.mydomain.com -Credential $Creds;
}

## 8. Move everything back into source control
./vault COMMIT $/$DBName;
./vault CHECKIN $/$DBName;

This may look slightly complicated, but there are really only a few commands at work. At the top, in the section labeled #1, we’re setting a number of variables. I defined values for the name of the database, the server, where we’re storing the scripts, a date value to deal with a small glitch in my source control software, and a set of credentials for email. That’s it. Nothing complicated.

After that, at #2, I have to get the date back about two minutes so that my source control software can find new files that get added because of SQL Compare.

From #3 down, things get interesting. First, I’m connecting up to my source control system and then I’m retrieving files from the system and checking them out for editing. All of that is done using commands from the source control system. If you’re using something other than Vault, your commands will be different, but you get the idea.

At #4, all the work is done by SQL Compare. It runs a sync between the scripts folder and the database in question. I also have SQL Compare generate a report. The output from SQL Compare goes into a variable for use later.

#5 is one of the slightly tricky parts. You need to know if SQL Compare found an identical data structure or if there were new or different objects. If you’ve worked with the command line before, you know that it outputs to the %ERRORLEVEL% variable. You can check that for the value, 86, which means no changes were found. It’s supposed to be $LASTEXITCODE, but it wasn’t working. Instead, I was able to capture the output from SQL Compare and line #12, $MyOutput[11], will match the string specified, “Error: The selected objects are identical or no objects have been selected in.” With this you can determine if there are changes you need to capture.

If you do have changes, #6 fires. Because Vault would not add new objects that had been added to the folders under source control automatically from the command line, I was forced to come up with a little bit of PowerShell code to do the trick. I got a directory listing through the Get-ChildItem. Then I filter that so I’m only looking at files and I filter those files so I’m only looking at ones that have been created prior to the data that I set back in #2. These will be the new files created by SQL Compare. All these processes are using the pipe provided by PowerShell so it’s a single command. It results in a call through for each-object to run the Vault command line to add the files to the appropriate location within Vault.

As part of the changes piece of the script, #7 will send an email using the Send-MailMessage cmdlet provided in PowerShell 2.0. The mail sent will have an attachment of the report file.

Finally, I commit the changes and check them in. Anything that wasn’t changed will be automatically unchecked.
That’s it.

Clearly, there are few statements that are unique to my system so paths and names will have to be adjusted for your system. Your source control system may also have completely different commands, so you’ll need to adjust that. But, as you can see, this is not hard to set up. With this in place and scheduled through SQL Agent, I can run this once a day and see exactly what has changed on the database in question, whilst making sure that the changes go into Source Control. I can always compare production and what’s stored in source control. Even better, I can pull a particular day or version from the source control system into a separate folder and then compare that to another day or version out of source control in order to report on changes between any two moments in time.

Using Backups to Audit Schema Changes

Since not every development team has source control, it’s worth exploring how you could do this using backups. It won’t be as powerful, but most of the functionality can be duplicated, except for getting the changes into source control, so you’re completely dependent on the backup being in place. If it’s missing, or old, you won’t be able to get a compare. Also, because you’re not going to source control, there’s no history of changes. However, the script would be very straightforward and only entail a couple of changes to two of the scripts above:

## 1 Setup variables
$DBName = "MovieManagement";
$Server = "myserver\myinstance";
$Backup = "C:\bu";
$pswd =  ConvertTo-SecureString "pswd" -AsPlainText -Force
$Creds = New-Object System.Management.Automation.PSCredential ("service@red-gate.com", $pswd)


## 2 Compare database to source code
set-Location "c:\Program Files (x86)\Red Gate\SQL Compare 9\";
$MyOUtput = ./sqlcompare /s1:$Server /db1:$DBName /bu2:$bu\$DBName.bak /sync /report:$bu\schemaDiff.html /reporttype:Simple /f;

## 3 Check for changes
IF ($MyOUtput[11] -ne "Error: The selected objects are identical or no objects have been selected in")
{
       ## 4 send an email with the report
       Send-MailMessage -From service@red-gate.com -To someone@red-gate.com -Subject "Schema Changes in database $DBName" -Body "The attached report shows changes that have occurred in the last 24 hours" -Attachments $Backup\schemaDiff.html -SmtpServer mail.domain.com -Credential $Creds;
}

Basically we’re just removing all the source control settings and then modifying the call to sqlcompare.exe to use /bu2 instead of /scr2, backups instead of scripts. That’s it. Now as long as there is a backup that is at least a day old, you can automate this script to generate an auditing report.

[Editor: OK, you've done the basic stuff. What about a few handbrake turns around the square?]

Seriously, that’s exactly what he said. No lie.
Fine.

Let’s assume that you’d still like to use source control, but, you don’t actually have direct access to your production servers, however, you do have access to the backup locations. Let’s also assume that you want to run this for all the backups, for all your servers, for all your databases, AND you’re going to go ahead and add this to all your source control systems, and then run a compare nightly on this mess (he had better like this):

## 1: Basic set up
$Scripts = 'C:\dbscripts'
##A little construct to deal with a shortcoming in Vault
$Date = Get-Date;
$Date.AddMinutes(-2);

## 2: Load the list of folders
set-location "C:\Program Files (x86)\SourceGear\Vault Client\";
./vault REMEMBERLOGIN -user admin -password 12345 -host VaultHost -repository MyRepo
[xml]$FolderList = ./vault LISTFOLDER -norecursive $


#1 Arguments with directories where FULL backups are kept are passed in.
get-ChildItem $args  | where-Object{($_.Extension -eq '.BAK') -and (!$_.PsIsContainer)} | foreach-object {
       ## 3: Get the full name which is the path, database name, and update the scripts folder
       $fullname = $_.FullName;
       $DBName = $_.Name.Replace('.bak','')
       $dbscript = $Scripts + '\' + $DBName
      
       ## 4: Determine if this folder exists in Vault
       set-location "C:\Program Files (x86)\SourceGear\Vault Client\";
       $Parsed = select-Xml -xml $FolderList -XPath 'vault/folder/folder[@name="$/$DBName"]'
       if ($Parsed.Node.Name -eq '$/$DBName')
       {
              ## 5: It does, so get the latest scripts
              ./vault GET $/$DBName;
              ./vault CHECKOUT $/$DBName;
       }
       else
       {
              ## 6: It doesn't, so add the folder in preparation for the objects
              ./vault ADD -commit $ $dbscript
       }
      

       ## 7: See if the folder exists, if not create one
       if  (!(test-Path $dbscript))
       {
              new-Item $dbscript -ItemType directory
       }

       ## 8: Do the comparison
       set-Location 'c:\Program Files (x86)\Red Gate\SQL Compare 9\'
       $MyOutput = ./sqlcompare /b1:$fullname /scr2:$dbscript /sync /report:$dbscript\schemaDiff.html /reporttype:Simple /f

       ## 9: Do the same trick to check on changes
       ##Determining if there are changes
       ##IF ($LASTEXITCODE -ne 0)
       IF ($MyOUtput[11] -ne "Error: The selected objects are identical or no objects have been selected in")
       {
              set-location "C:\Program Files (x86)\SourceGear\Vault Client\";
              ##There are changes, add them all to source control
              Get-ChildItem $dbscript -Recurse |  where-Object{!$_.PsIsContainer} | where-Object{$_.CreationTime -gt $Date} | foreach-object {./vault ADD -commit ("$" + $_.FullName.Replace($Scripts,"").Replace("\"+$_.Name,"").Replace("\","/")) $_.FullName}
             
       }

       ##clean up
       ./vault COMMIT $/$DBName
       ./vault CHECKIN $/$DBName


}

[Editor: Yeah: cool.]

The way this script works is pretty straight forward. You can pass it multiple directories through the command line, and it will read each directory and pull all the files from them. The assumption is that the backup file name is the same as the database name (and yeah, I know that’s not always the case, but it’s usually part of the name, fix that yourself). We go through the same set-up in #1 that we did in the previous script. At #2 I get a list of the root folders from the source control system so that I can determine which databases need to be added and which ones I can check out and add or update stuff. At #3 I set up a few variables to make the code easier to work with, and at #4 I made sure I had a folder ready to receive any new databases because if there is no folder, Vault isn’t happy. #5 is where I perform GET & CHECKOUT operations if folders already exist. If no folder exists, then I create one at #6. At #7 I create physical file folders for the scripts created in SQL Compare. The comparison is done at #8. Finally at #9, I do the same trick as before to add files to Vault.

That’s it. It really wasn’t that hard to work through all the files. I didn’t bother to set up email this time because I suspect you wouldn’t be happy to get an email with X number of attachments sent to you once a day. However, it wouldn’t be an issue to take care of that as well.

Conclusion

Whether you’re dealing with the cow town Saturday night of all ‘sa’ access or the ordered world of a publicly traded company, you’re going to need to audit changes on your system. Combining the scripting capabilities of PowerShell and all its quick & easy power, with the command line facilities of your source control system and SQL Compare seems to be just the combination to get this problem under control.

Connect SQL Server to Vault and all other source control systems in less than 5 minutes. Find out how.

Author: Grant Fritchey

DBA: WIN A TRIP TO SPACE !!!

Folks....Red Gate is giving you a chance to win the experience of a lifetime - a voyage into space. Yes, the final frontier! If you complete their video quiz and then gather the most votes, you could be the first DBA in space !!!

Find more info here...

Windows Microsoft SDL - Developer Starter Kit

The Microsoft SDL - Developer Starter Kit provides a compliation of baseline developer security training materials on the following core Microsoft Security Development Lifecycle (SDL) topics:

1. secure design principles; 
2. secure implementation principles; 
3. secure verification principles; 
4. SQL injection; 
5. cross-site scripting; 
6. code analysis; 
7. banned application programming interfaces (APIs); 
8. buffer overflows; 
9. source code annotation language; 
10. security code review; 
11. compiler defenses; 
12. fuzz testing; 
13. Microsoft SDL threat modeling principles; and 
14. the Microsoft SDL threat modeling tool.

Each set of guidance contains Microsoft Office PowerPoint slides, speaker notes, train-the-trainer audio files, and sample comprehension questions. All materials have limited formatting so that you can leverage the content to achieve broader, enhanced adoption of Microsoft SDL principles in your development organization. Learn more...

INFORMATION_SCHEMA.101

If you are new to SQL Server you may have trouble finding the tables you are looking for in your database or which columns in any table use the SmallDateTime data type. You can use SQL Server Management Studio (SSMS) to track this down but it will be neither pretty nor quick. But suppose you need to know which stored procedures have cross database joins to a specific database or which user-defined functions have a hardcoded product name in a Case statement - SSMS won't give you much help. If only there were a simple and standardized way to find this information using TSQL - wouldn't that be great? Well, as it happens, that data is available in the INFORMATION_SCHEMA views (ISVs) in each database.

From BOL: "Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA."

This means you can use the same code to get metadata from any version of SQL Server from SQL 2000 on. There are other ways to get this information by querying system objects but these may change from release to release. In fact since ISVs conform to the ISO standard it also works on other platforms such as DB2.

Note that the information given is primarily for user defined database objects rather than system metadata. Here is a list of ISVs along with the code that produced the list. (I find it ironic there isn't an ISV for this.)

Select name
 From sys.all_views
 Where schema_id = 3
 Order By name

name
-----------------------
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINE_COLUMNS
ROUTINES
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS

We are not going to look at all the views, instead we will focus on three of the most useful for the average DBA to use cruising around a database. Once you get the feel for the naming conventions in these views you will find the rest familiar also.

Finding Tables

If you need to know where all the address data in your DB is stored you might write the following query.

Select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
 From INFORMATION_SCHEMA.TABLES
 Where TABLE_NAME like '%Address%'

TABLE_CATALOG            TABLE_SCHEMA           TABLE_NAME          TABLE_TYPE
------------------------ ---------------------- ------------------- ------------------
AdventureWorks           Person                 Address             BASE TABLE
AdventureWorks           Person                 AddressType         BASE TABLE
AdventureWorks           Purchasing             VendorAddress       BASE TABLE
AdventureWorks           Sales                  CustomerAddress     BASE TABLE
AdventureWorks           HumanResources         EmployeeAddress     BASE TABLE

Finding Colunms

Suppose you are considering changing column data types from DateTime to DateTime2. Rather than cracking each table in SSMS this might be a good place to start.

Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, ORDINAL_POSITIONFrom INFORMATION_SCHEMA.COLUMNSWhere DATA_TYPE Like '%Date%'
Order By TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE ORDINAL_POSITION
------------------------ ------------------- ----------------------- ----------------- -----------------------------
dbo AWBuildVersion VersionDate datetime 3
dbo AWBuildVersion ModifiedDate datetime 4
dbo DatabaseLog PostTime datetime 2
dbo ErrorLog ErrorTime datetime 2
HumanResources Department ModifiedDate datetime 4
HumanResources Employee BirthDate datetime 7
HumanResources Employee HireDate datetime 10
The ORDINAL_POSITION is also handy when you are trying to locate a single column out of a 150 column table. (Oh, come on, you know you have them.)

Finding Code

Finally, let's see how you can query against the T-SQL code in stored procedures and user-defined functions. Suppose you have an instance with multiple DBs that have been together for a long time and have grown intertwined through cross-database joins. Not a problem as long as everybody stays on the instance but suppose you need to move one of them? You can query the ROUTINES view to find cross-database joins (or any string) in the underlying code as follows:

Select ROUTINE_NAME
 From INFORMATION_SCHEMA.ROUTINES
 Where ROUTINE_DEFINITION Like '%Bob.%'
 Or ROUTINE_DEFINITION Like '%[Bob].%'

ROUTINE_NAME
------------------------------------
uspGetBillOfMaterials
uspGetWhereUsedProductID
usp_BobsPeeps_S

This will return the name of every user-defined function or stored procedure in the DB with a cross-database join to Bob.

There is one peculiarity you should be aware of here though. Since the ROUTINE_DEFINITION column is nvarchar(4000) an exceptionally large chunk of code will generate multiple rows in the view. If, and it's a big if, the string you are looking for falls across the 4000 character mark the search will not return a row. You could code for this but I, being a good Calvinist, can accept that I was not ordained to find the string and move on with my life.

Summary

The INFORMATION_SCHEMA views are an easy to use set of tools to get user object metadata from any (modern) version of SQL Server as well as other major relational database engines. The naming convention is consistent and the ISO standardization ensures future compatibility.

Author: Tom Powell

Adding a Node to a SQL Server 2005 Failover Cluster

Setting up multi-node clustered SQL instances from scratch is common practice for companies that want to implement high availability, but adding a new node to an existing clustered SQL instance that has been running for a while may have a few sticking points, especially for a SQL 2005 instance. In this series, I will walk through how to add new node for existing clustered SQL Server instance for SQL 2005 environments, and applying a post-configuration service pack.

Step by step procedure for adding a node

Below, I detail step by step instructions for how to add a node and then apply a service pack or hot fix afterward.
Step 1: Log onto the primary node for the existing SQL 2005 Clustered instance. The primary node is the clustered node where your SQL 2005 clustered instance is currently running.

Step 2: It's always advisable to run the setup procedure by a service account with administrative privilege on all the clustered nodes. To launch Add or Remove Programs in Control Panel with a service account, follow these steps:

1.       Navigate down to C:\WINDOWS\system32
2.       Look for appwiz.cpl

3.       Right click on appwiz.cpl, select click RunAs, then specify service account "User Name" and "Password" and click OK

Step 3: Once you have seen the Add or Remove Program window below, it means you have successfully launch it with the designated domain account.

Step 4:  If your OS is 64 bit, look for "Microsoft SQL Server 2004 (64-bit)," and then click Change. The wizard for "Microsoft SQL Server 2005 Maintenance" will start as below (I have masked the instance name for Reporting Services and Database Engine for confidentiality of the company) In your environment, you should expect to see the instance name for the masked portion. You can also see common components that were already installed on the server.

Step 5: If you have multiple SQL 2005 instances then you will need to perform one instance at a time. Here I only have one SQL 2005 clustered instance, so I selected the instance by clicking the radio button and then clicking Next.

Step 6: In the Feature Maintenance page, select a component to change; Database Engine (Clustered) is selected by default. Click Next, then the SQL Server 2005 System Configuration Checker starts.

After few seconds, the Welcome page for "Microsoft SQL Server installation Wizard" will pop up, as below.

Step 7: Simply click Next to proceed

Step 8: Installation wizard will perform system check and make sure it can proceed without Error or critical warning.  Review all the warning and error message provided by System Configuration Checker. Address any threatening warning and error if needed, then re-launch from Step 2.
Click Next once system configuration check is completed.

Step 9:  in the Change or Remove Instance page, select Maintain the Virtual Server.

Step  10: On the Cluster Node Configuration page. Here is where you will see the ActiveNode, PassiveNode and the new node you'd like to add. Since the installation wizard needs to be launched from the active node for the clustered instance, you will see the machine you logged onto appears on "Required node." For the existing passive node for the clustered instance, the server name will appear on "Selected nodes," and the new node you are working on to add into the clustered instance will appear on "Available nodes."
Simply click Add to move the available node to selected nodes. You can now see both Passive node and the new node being listed under "Selected nodes," like the below screenshot.

Click Next to proceed.

Step  11: Remote Account Information page shows up. Here you just need to provide the password for the domain account you used to launch the Setup wizard from step 2. Make sure the login you used to launch the wizard has administrative permission on all the three clustered nodes for the instance you are operating. Then click Next to proceed.

Step 12: Here you will be asked to provide the password for the existing service account used to run SQL Server service, SQL Agent service and Browser service.  Once you fill in the correct password information, click Next.

Step 13: On the Error and User Report Settings page, depending on your company's policy, select either or not to send error reports and usage data to Microsoft, then click Next.

Step 14: Ready to Update page shows up, click Install.

Step 15: Configuring Microsoft SQL Server page will show up, do not click anything, let it run through progress bar.

Step 16: Then a windows explorer navigation window will pop up; here you have to browse through your directory to help the installation wizard to locate SqlRun_SQL.msi. You can find this msi under SQL 2005 installation binary folder ...\disk1\Setup, depending on where you place the SQL 2005 install binary. You can also do a search on SqlRun_SQL.msi under the install binary to find its exact location.

Step 17: Once you successfully locate the SqlRun_SQL.msi, click OK to continue.

Step 18: Don't click on anything, Let the setup wizard do its duty.

Step 19: After a few minutes, you will receive the above warning message that indicates that existing nodes for the SQL 2005 clustered instances are already patched. So you will also need to patch the new node to match rest of the clustered nodes. Click OK to acknowledge.


Step 20: You will see this "Setup Progress" page once Setup Wizard is about to install components on the new node. Do not click anything and wait for its setup progress page.

Step 21: Finally, Setup Wizard has come down to the business of installing all necessary components on the new node. Please wait as it indicates on the page.

*If you are adding two new nodes instead of one new node, the setup wizard will provide a drop down so you can scroll the node and see the install progress on each node, like the below diagram.

The installation procedure to add two new clustered nodes and to add one new clustered node are exactly the same, except that you can scroll the drop-down list of server names to review the installation progress. Below, I will continue to use the screenshot I took for adding one new node to an existing clustered SQL 2005 instance.

Step 22: Keep your fingers crossed and hopefully you can see the Green Check Mark that indicates successful setup of the respective components; below three screenshots display successful progress of the installation.

Step 22: Once you see that all components have the status "setup finished," you can relax and click Next because Setup wizard has successfully install the selected SQL components on the new node.

Step 23: You can review the setup log by clicking the summary log, or clicking Finish to complete the procedure of adding a new node.

Step 24: It's a good practice to recycle your affected servers. Make sure the SQL 2005 clustered instance is back online and healthy after adding the new node. Do not failover the SQL instance to the newly added node just yet.

Author: Claire Hsu