Creating Integer Bands in T-SQL (e.g. Age)

One of the roles for my team is to de-identify data for research. Very often we are asked for the age of the person to be included in the dataset, however, my organisation considers this to be identifiable data. Therefore, we convert the age into age bands. The default is five year bands, but it really depends on the size of the data set as to what is the most appropriate age banding. This short post shows a more streamlined way of creating age bands (and indeed bands for any other integer data, e.g. years)

Getting the age

The age should be calculate from the date of a person’s date of birth to the date of the event of interest, for example the date of a diagnosis. In T-SQL, this is done using the DATEDIFF function, like this:

SELECT DATEDIFF(DD, DateofBirth, DateOfEvent) / 8766 AS Age

(The difference in days between the two dates divided by the number of hours in a year (365 1/4 days)).

The way I used to create the age bands

This method uses long case statements which are a pain to make and it is easy to make errors because they are usually created with cut, paste and edit operations. Here is an example which only shows ages banded for an older population. If ages were banded for everyone from 0 to 100 years old then there would be 20 WHEN … THEN clauses.

CASE WHEN Age < 55 THEN '[<55]'
     WHEN Age >=55 AND Age <= 59 THEN '[55-59]'
     WHEN Age >=60 AND Age <= 64 THEN '[60-64]'
     WHEN Age >=65 AND Age <= 69 THEN '[65-69]'
     WHEN Age >=70 AND Age <= 74 THEN '[70-74]'
     WHEN Age >=75 AND Age <= 79 THEN '[75-79]'
     WHEN Age >=80 AND Age <= 84 THEN '[80-84]'
     WHEN Age >=85 AND Age <= 89 THEN '[85-89]'
     WHEN Age >=90 AND Age <= 94 THEN '[90-94]' 
     WHEN Age > 94 THEN '[>94]'
END AS AgeBand

A neater banding method

This method seeks to get rid of the unnecessary repetition by calculating the bands. In this example I will be using the same bands as in the example above.

The first step is to convert all the ages to the bottom of the age band. This uses the modulo operator. Assuming the age has already been calculated, the code is like this:

SELECT CASE WHEN Age % 5 = 0 THEN Age ELSE Age - (Age % 5) END AS BottomOfAgeBand

If we want five year bands then we calculate the remainder with the age divided by 5. If the output is 0 then the age is already at the bottom of the age band. If the there is an output we need to subtract the remainder from the age to bring it to the bottom of the age band.

SELECT CASE WHEN BottomOfAgeBand < 55 THEN '[<55]'
            WHEN BottomOfAgeBand > 90 THEN '[>94]'
            ELSE '[' + CONVERT(VARCHAR, BottomOfAgeBand) + '-' + CONVERT(VARCHAR, BottomOfAgeBand + 4) + ']'
       END AS AgeBand

Assuming that the above has been carried out we can then format the output in age bands. this only requires three WHEN … THEN clauses. One for ages below the lower banding threshold (55 in the example above), one for ages above the upper banding threshold (94 in the example above) and one for all of the age bands in between:

Building HTML with Groovy MarkupBuilder

Sometimes you may want to build up a portion of HTML to render from your application. This is very easy to do with the MarkupBuilder class in Groovy. Here I show you how to do this and also share with you a few gotchas that I have encountered.

First, you need to import groovy.xml.MarkupBuilder into your controller.

import groovy.xml.MarkupBuilder

Second, you need your data. I am going to use data in the form of a Map of Maps. I have retrieved these data using a service in my example below. Next, in your method you need to create instances of the StringWriter class and the MarkupBuilder class. The constructor for MarkupBuilder takes the instance of the StringWriter class as a parameter:

class SomeController {

    def mapsOfDataMaps = someService.getSomeData()

    def someMethod() {
        def writer = new StringWriter()
        def builder = new MarkupBuilder(writer)
    // (1)
    }
}

Next I’m going to use the builder to build a table for some data I have retrieved from the database. This code is inserted in (1) in the above snippet. I am starting with the table headers. (I recommend that you always use the message.properties in the i18 folder for all labels and not use string literals. This means that if you need to rename anything shown on screen you only do it in one place and it is done consistently.):

builder.table {
    thead {
        tr {
            // Table headers using the message.properties code in i18
            th (message(code: 'table.header1.label'))
            th (message(code: 'table.header2.label'))
            th (message(code: 'table.header3.label'))
            // Table headers as string literals
            th ("Header 4")
        }
    }
    // (2)

Now we create the table body. I will highlight different examples of use. This follows on from (2) in the snippet above:

    tbody {
        mapsOfDataMaps.each { map->

            // The row I have used as a link for further details. This calls a JavaScript function and also has a tool tip.
            tr (onClick: 'javaScriptFunction(\'' + map.value.rowId + '\')', 'data-toggle': 'tooltip', title: 'Click for further details') {

               // A straight forward display of the data
               td (map.value.column1)

               // I put an icon here for opening up a form as a document from the database. Note curly braces here. Again onclick event uses a JavaScript function to do the work of opening the form.
               td {
                   mkp.yieldUnescaped img( src: 'form-icon.png', alt: 'Form', width: '20', height: '20',  'data-toggle': 'tooltip',  title: 'Click to view the form.', onClick: 'getDocumentWithJavaScript(\'' + map.value.rowId + '\')')
               }

               td (map.value.column3)
               td (map.value.column4)
            } // End tr
        } // End tbody
    } // End table
} // End builder

You should get the idea how this builds up from the example of a table above. Imagine you want to build a form. Then some of these elements are shown below.

// Example of a text field. You can add event handlers in also.
input(name: "textField1", id: "textBox1", onchange: "updatetextField1('" + Id + "')", type: "text", value: map.value.textField1Value)

// Example of text area. Note how to add the value between the curly braces and the use of a ternary operator to handle null values.
textarea(name: "textarea1", id: "textarea1", type: "text"){mkp.yield(map.value.textArea1Value? map.value.textArea1Value: "")}

// Example of a select input. You must specify the name of the MarkupBuilder instance for this to work. Also, it is more complicated identifying with of the options was previously selected.
builder.select (name: "select1", id: "select1") {

    // First, for null selection
    if (map.value.selectableItem== '') {
        option('', value: '', selected: 'selected') {}
    } else {
        option('', value: '') {}
    }

    // Other options
    if (map.value.selectableItem == 'Option 1') {
        option('Option 1', value: 'Option 1', selected: 'selected') {}
    } else {
        option('Option 1', value: 'Option 1') {}
    }
    if (map.value.selectableItem == 'Option 2') {
        option('Option 2', value: 'Option 2', selected: 'selected') {}
    } else {
        option('Option 2', value: 'Option 2') {}
    } // End if
} // End builder.select

// A button with an onclick handler for calling a JavaScript function
input(class: "buttons", id: "button1", type: "button", value: message(code: 'button.label'), onclick: "someFunction('someValue')")

Some attributes by their very presence affect the output, for example, disabled and checked. An input will be disabled if the attribute is disabled=”true” or disabled=”false” and a checkbox will be checked if the attribute is checked=”true” or checked=”false”. How can we handle this in our form builder?

// Example of a checkbox. The markup builder will not write markup for null attributes. So, in this example if the value is not true then the checked attribute will be null are not written in the markup.
builder.element {
    // This is normally 'false' by default but in this case we need it to be true
    setOmitNullAttributes(true)
    ... 
    input(name: "checkbox1", id: "checkbox1", type: "checkBox", checked: (map.value ? "checked" : null))
}

I hope that if you do choose to use MarkupBuilder for creating HTML tables and forms that this assists you.

Splitting Data in an SQL Database Column

In a previous post, Importing “Weird” Data Files into SQL Server, I described a couple of unusually structured data files and what I had to do to get them into an SQL Server database table. Each full row of data is imported into one database column. Now I am going to describe how I went about splitting the data from this column into multiple columns.

Creating a Consistent Number of Column Delimiters

One of the “weird” file types was tab-delimited and had an irregular number of tabs in each row of data. In fact, there were between 8 and 13 tabs in each row. I needed to make sure that each row had the same number of tabs, i.e. 13; so that I could easily split them.

UPDATE [Schema].[Table]
 SET Data = CASE WHEN 13 - (DATALENGTH(Data) - DATALENGTH(REPLACE(Data, CHAR(9), ''''))) = 1 THEN Data + CHAR(9)
 WHEN 13 - (DATALENGTH(Data) - DATALENGTH(REPLACE(Data, CHAR(9), ''''))) = 2 THEN Data + CHAR(9) + CHAR(9)
 WHEN 13 - (DATALENGTH(Data) - DATALENGTH(REPLACE(Data, CHAR(9), ''''))) = 3 THEN Data + CHAR(9) + CHAR(9) + CHAR(9)
 WHEN 13 - (DATALENGTH(Data) - DATALENGTH(REPLACE(Data, CHAR(9), ''''))) = 4 THEN Data + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9)
 WHEN 13 - (DATALENGTH(Data) - DATALENGTH(REPLACE(Data, CHAR(9), ''''))) = 5 THEN Data + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9)
 ELSE Data
 END 

This works by comparing the length of the data in the string with the length of the data in the string when the tab characters are replaced. The result is deducted from 13 to determine how many additional tab characters need to be appended so that each row has 13 tabs in it. The case statement causes the correct number of tabs to be added.

If anyone has a neater solution, I would be very interested to have your comments.

Splitting the Data in the SELECT

We could if we chose split the data using the  SUBSTRING function combined with CHARINDEX and CHAR functions in the SELECT part of the query, e.g.

SELECT  SUBSTRING(Data, 1, CHARINDEX(CHAR(9), Data) - 1) AS Column0
 ,SUBSTRING(Data, CHARINDEX(CHAR(9), Data) + 1, (CHARINDEX(CHAR(9), Data, CHARINDEX(CHAR(9), Data) + 1) - CHARINDEX(CHAR(9), Data) - 1)) AS Column1
 FROM [Schema].[Table]; 

For the Column0, we know that the start index for the SUBSTRING function is 1, but we require the CHARINDEX function to determine the length of the substring (which is 1 less than the index of the first tab character, which we have to identify using the CHAR function). This is not too complicated. For the 2nd column, we need to determine the index of the first tab character and the substring starts at the next index value. The length is determined by finding the index of the 2nd tab by starting to look for it from the index of the first tab plus 1; then we have to take away the index of the first tab the take away another 1 to get the length of the substring. Getting more complicated. The last column required would be slightly easier since we can use the LEN (or DATALENGTH, if required) function and take away the index of last tab.

I had 14 columns. You can see how complex the code would become if I were to pursue this approach. Each column is determined by repeating calculations already done for the preceding column. We need a method of preserving the indexes of the tabs. That leads me to …

Using CROSS APPLY to Split Data

The APPLY operator specifies that the table source (table-valued function or table-valued expression) on the right of the  operator is evaluated against every row of the record set on the left. Then they are joined together, so that all the columns from both sources appear in the result set. We can use multiple CROSS APPLYs to avoid repeatedly calculating the index of each tab.

SELECT  Column1
    ,Tab1
    ,Column2
    ,Tab2
    ,Column3
    ,Tab3
    ,Column4
    ,Tab4
    ,Column5
    ,Tab5
    ,Column6
    ,Tab6
    ,Column7
    ,Tab7
    ,Column8
    ,Tab8
    ,Column9
    ,Tab9
    ,Column10
    ,Tab10
    ,Column11
    ,Tab11
    ,Column12
    ,Tab12
    ,Column13
    ,Tab13
    ,Column14
  FROM [Schema].[Table]
 CROSS APPLY (SELECT  CHARINDEX(CHAR(9), Data) AS Tab1) AS GetTab1
 CROSS APPLY (SELECT  SUBSTRING(Data, 1, Tab1 - 1) AS Column1) AS GetColumn1
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab1 + 1, (CHARINDEX(CHAR(9), Data, Tab1 + 1) - Tab1 - 1)) AS Column2
      ,CHARINDEX(CHAR(9), Data, Tab1 + 1) AS Tab2
     ) AS Column2AndTab2
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab2 + 1, (CHARINDEX(CHAR(9), Data, Tab2 + 1) - Tab2 - 1)) AS Column3
      ,CHARINDEX(CHAR(9), Data, Tab2 + 1) AS Tab3
     ) AS Column3AndTab3
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab3 + 1, (CHARINDEX(CHAR(9), Data, Tab3 + 1) - Tab3 - 1)) AS Column4
      ,CHARINDEX(CHAR(9), Data, Tab3 + 1) AS Tab4
     ) AS Column4AndTab4
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab4 + 1, (CHARINDEX(CHAR(9), Data, Tab4 + 1) - Tab4 - 1)) AS Column5
      ,CHARINDEX(CHAR(9), Data, Tab4 + 1) AS Tab5
     ) AS Column5AndTab5
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab5 + 1, (CHARINDEX(CHAR(9), Data, Tab5 + 1) - Tab5 - 1)) AS Column6
      ,CHARINDEX(CHAR(9), Data, Tab5 + 1) AS Tab6
     ) AS Column6AndTab6
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab6 + 1, (CHARINDEX(CHAR(9), Data, Tab6 + 1) - Tab6 - 1)) AS Column7
      ,CHARINDEX(CHAR(9), Data, Tab6 + 1) AS Tab7
     ) AS Column7AndTab7
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab7 + 1, (CHARINDEX(CHAR(9), Data, Tab7 + 1) - Tab7 - 1)) AS Column8
      ,CHARINDEX(CHAR(9), Data, Tab7 + 1) AS Tab8
     ) AS Column8AndTab8
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab8 + 1, (CHARINDEX(CHAR(9), Data, Tab8 + 1) - Tab8 - 1)) AS Column9
      ,CHARINDEX(CHAR(9), Data, Tab8 + 1) AS Tab9
     ) AS Column9AndTab9
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab9 + 1, (CHARINDEX(CHAR(9), Data, Tab9 + 1) - Tab9 - 1)) AS Column10
      ,CHARINDEX(CHAR(9), Data, Tab9 + 1) AS Tab10
     ) AS Column10AndTab10
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab10 + 1, (CHARINDEX(CHAR(9), Data, Tab10 + 1) - Tab10 - 1)) AS Column11
      ,CHARINDEX(CHAR(9), Data, Tab10 + 1) AS Tab11
     ) AS Column11AndTab11
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab11 + 1, (CHARINDEX(CHAR(9), Data, Tab11 + 1) - Tab11 - 1)) AS Column12
      ,CHARINDEX(CHAR(9), Data, Tab11 + 1) AS Tab12
     ) AS Column12AndTab12
 CROSS APPLY (SELECT  SUBSTRING(Data, Tab12 + 1, (CHARINDEX(CHAR(9), Data, Tab12 + 1) - Tab12 - 1)) AS Column13
      ,CHARINDEX(CHAR(9), Data, Tab12 + 1) AS Tab13
     ) AS Column13AndTab13
 CROSS APPLY (SELECT  REPLACE(SUBSTRING(Data, Tab13 + 1, LEN(Data) - Tab13), CHAR(13), '') AS Column14
     ) AS LastColumn;

This last method is the one that I chose.

Pivoting Data in SQL Server with Dynamic Column Listing

One of the issues with pivoting data in SQL Server is that you always have to have the list of values from the column that needs pivoting and then you have to hard code the values as column headers. Here’s a way round that:

-- Declare the variables used
DECLARE @SQL VARCHAR(MAX), @Columns VARCHAR(MAX);
-- Get all the values from the lookup table that will form the column headers of your pivoted data
SET @Columns = (SELECT STUFF((SELECT ', ' + LookUpColumn FROM TableWithLookupData FOR XML PATH('')), 1, 2, ''));

-- Use these values in the SQL pivot statement and turn this into a string
SET @SQL = '
 SELECT FirstColumn AS FirstColumn,' + @Columns + '
 FROM
(
SELECT LookUpColumnName
,FirstColumn
,COUNT (DISTINCT(ID)) AS CountResult
FROM  DataTableThatNeedsPivoting
WHERE FirstColumn IS NOT NULL
GROUP BY LookUpColumnName, FirstColumn
) AS SourceTable
PIVOT
(
SUM(CountResult) FOR LookUpColumnName IN (' + @Columns + ')
) AS PivotTable
';

-- Execute the SQL 
EXECUTE (@SQL);

  

Importing “Weird” Data Files into SQL Server

I have recently come across two types of data file that have a strange structure. They do not fit into the standard for files and it has consequently been challenging to get the data into an SQL Server database in a form that is useful.

File Description

One file type is tab delimited. However, each valid row has between 8 and 13 tabs. The problem with this is that I cannot simply import it into an SQL Server database using the built-in SQL Server Import and Export Wizard nor can I use the added functionality that comes with using full SQL Server Integration Services (SSIS). I cannot use a “Delimited” import nor a “Ragged Right” import with fixed width columns.

The other weird file type I came across carries a csv extension.  It contains fixed width columns for the first few columns separated also by commas. However, one of these columns contains free text notes with commas in them. The columns are not text qualified. The last few columns contain sets of fixed-width sub-fields. There can be one or more sets between the comma delimiters. Therefore, I can neither use a “Delimited” import nor a “Ragged Right” import with fixed-width columns with this file type either.

Getting the Data into the Database

I decided in both cases that the best way to handle these types of files is simply to import the whole row of data into one column of an SQL Server database table using the Ragged Right option. There are more options to split the data once the data is held in the database. In a future post, I will describe how to split data from one column into multiple columns.

Grails Breadcrumbs Issue in IE8

Using the Grails Breadcrumbs Plugin with Grails 2.3.7 resulted in a problem. The plugin worked as expected in Google Chrome and Mozilla Firefox, but I was having issues with IE8. I have to use IE8 since that is the default browser in my organisation. We have installed Google Chrome Frame (which is itself no longer supported by Google). Google Chrome Frame solves most of the issues that I have when developing for IE8 but not this one.

The Breadcrumbs plugin, by default removes duplicates from the list of crumbs. It does remove duplicates in Firefox and Chrome, but not in IE8. There are many duplicates in my implementation because I have used

<crumbs:trail selector="title" />

The title text appears in several places on the page and there are is a varied amount of white space either side of the text. IE8 appears to send all this white space to the server. The code in the controller that removes duplicates sees these strings with varied amounts of white space and thinks they are all different. The dupliacates do not get removed. The other browsers seem to send the Strings trimmed and so the duplicates are removed.

I have fixed the problem in IE8 simply by modifying the line in the controller from

def crumb = new Crumb(name: params.name, href: params.href)

to

def crumb = new Crumb(name: (params.name).trim(), href: params.href)

Trimming the name parameter solves the problem. The strings can be properly compared and the duplicates removed from the crumb trail.

NHS Number Validation

If you’re like me and work with NHS numbers regularly, then you need to be aware that the NHS number has a built in check digit. To protect against errors I recommed that you use this function to check the validity of the NHS numbers in your database. I have implemented this function as a check constraint on my NHS number columns.

Health Analyst

I’ve been working on NHS number validation recently so thought I’d share my code. Although there is guidance available on how to validate NHS numbers using modulus 11 check digit valiation, no one seems to have put up any code for it. It’s simple but hopefully it’ll be useful.

It will obviously not tell you whether a particular number has been assigned to a patient. It will only tell you if the number has a valid check digit. It is still a useful tool for checking large volumes of records for transcription errors and the like.

I’ve succesfully used it in three different languages: T-SQL, Excel VBA and R. In all three cases it is implemented as a user defined function which will return either 1 or 0 (1 = valid, 0 = invalid).

T-SQL

CREATE FUNCTION [dbo].[fnNHSvalidation] (@NHSnumber NVARCHAR(10))
RETURNS INTEGER
AS

BEGIN

/*Declare variables to hold values for…

View original post 565 more words

Grails – How to avoid the “Address already in use” Error

I discussed last week how to handle this error should it arise when issuing a run-app command:

Error Server failed to start for port 8080: Address already in use: JVM_Bind

I have been getting it a lot recently following an upgrade to Grails 2.3.7 and updating my Groovy and Grails Tool Suite (GGTS) to 3.6. Actually, to be more accurate I have been getting this error:

Error Server failed to start for port 8100: Address already in use: JVM_Bind

My variation is because I never run my developmental applications using the default port of 8080. I reserve this port for the test applications running under Apache Tomcat on my machine. To use a different port for running an application in the development environment you need to change the run-app command to run-app -Dserver.port=8100. Obviously, you can change the port number to whatever unused port number takes your fancy.

Now when I researched the subject about avoiding the “Address already in use” error, the answer was always the same use the stop-app command. This never worked for me. I still got the error.

Yesterday, I had a “light-bulb” moment. Maybe, I need to specify the port when I use the stop-app command; otherwise how does Grails know which application to stop running. (This because Grails 2.3 uses “Forked Execution”. See http://grails.io/post/43484836985/road-to-grails-2-3-forked-execution for more information.)

Sure enough if you use a run-app command and specify the port number your stop-app command must mirror it. For example, in my case when I want to stop an application running on port 8100, the command is stop-app -Dserver.port=8100.

I’ve not had to use “netstat” to terminate the process for 24 hours now!

Grails – Address Already in Use Error

Your testing your Grails application. You accidentally close your console/Command Prompt without first terminating your running grails application. Or, your application continues running even after attempting a “stop-app” command. The next time you execute “grails run-app” you get an error message similar to the following:

Error Server failed to start for port 8080: Address already in use: JVM_Bind

How can you find out what process is using the port and then terminate the process so that you can run your Grails application again?

Open a command prompt and type:

netstat -a -o

In the list provided look for the process that is using the port you want to use for your Grails application. The listing you are looking for will be in this format:

TCP   [HostName]:8080       [HostName].[DomainName].int:0  LISTENING       15828

As you can see the port number appears after your hostname and a colon. The ProcessID (PID)  appears at the end of the line.

Make a note of the PID.

Open Task Manager. (A neat shortcut is CTRL + SHIFT + EXC). Go to the “Processes” tab. Open the “View|Select Columns” dialogue box from the menu bar. Ensure that there is a tick in the “PID (Process Identifier)” box, and click OK. Look for the PID that you noted from the “netstat” command and select it from the list of processes. Click on End Process.

Next time that you run “grails run-app” you will not get the port in use error.

Deleting a schema from an SQL Server Database

I am developing a new database, and I don’t want to keep recreatiing the database and re-importing the data all the time. The import scripts take a while to execute. Therefore, since I am developing in schemas, it makes more sense just to delete and recreate the particular schema that I am working on. How nice it would be if Microsoft allowed you to simply enter DROP SCHEMA [schema_name]; but they don’t.

Originally, I set about writing IF EXISTS ... DROP statements for all the objects in the schema. This became clunky and time consuming, however.  There must be a better way.

The foreign key constraints have to be dropped first and then the tables can be dropped and then finally the schema itself can be dropped, if required. If I pull out of the system databases the details of the foreign key constraints and tables, I can build dynamic SQL statements to drop the objects. Since I dislike cursors, I have chosen to use a WHILE loop. Here is my implementation:

DECLARE @SchemaName VARCHAR(50) = '[schema_name]'

DECLARE @DropFKStatement VARCHAR(255);
WHILE EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
AND TABLE_SCHEMA = @SchemaName)
BEGIN
SET @DropFKStatement =
(SELECT TOP 1
'ALTER TABLE ' + TABLE_SCHEMA +
'.' + TABLE_NAME +
' DROP CONSTRAINT ' + CONSTRAINT_NAME + ';'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
AND TABLE_SCHEMA = @SchemaName);
PRINT @DropFKStatement;
EXECUTE (@DropFKStatement);
END

DECLARE @DropTableStatement VARCHAR(255);
WHILE EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @SchemaName)
BEGIN
SET @DropTableStatement =
(SELECT TOP 1
'DROP TABLE ' + TABLE_SCHEMA +
'.' + TABLE_NAME + ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @SchemaName);
PRINT @DropTableStatement;
EXECUTE (@DropTableStatement);
END

IF EXISTS (SELECT *
FROM sys.schemas
WHERE name = @SchemaName)
BEGIN
DROP SCHEMA [schema_name];
END