Dot Net Technologies -- MS Tech talk and more - SQL Tips

Tip of the Day: T-SQL Script to rename bulk columns

Daryl — Wed, 04 Aug 2010 04:22:15 GMT

The project I am currently working on is a port from a horribly over-engineered Oracle system to a much more manageable SQL Server system. One of the things we needed to do was rename a lot of fields to something more usable. For instance, every table had a field titled "Ended" but we felt it was easier to think of that record as "Deleted". Rather than go through every table (and it wasn't in every table, meaning you needed to manually check each one to verify), I opted a scripting solution.

Below is a T-SQL script which will generate the sp_rename scripts for all the tables where the target field is found. A job which would have taken hours and been prone to errors is now a simple task taking a minute.

DECLARE @OldFieldName VARCHAR(50),
@NewFieldName VARCHAR(50) SET @OldFieldName
= 'SomeOldFieldName' SET @NewFieldName
= 'MyNewFieldName' select 'sp_RENAME
' + CHAR(39)
+ 'OFC.' +
TABLE_NAME + '.' +
@OldFieldName + CHAR(39)
+ ',
' + CHAR(39)
+ @NewFieldName + CHAR(39)
+ ',' + CHAR(39)
+ 'COLUMN' + CHAR(39)
+ CHAR(10)
+ 'GO' + CHAR(10) from INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME
= @OldFieldName ORDER BY TABLE_NAME

The script above will generate the T-SQL code to rename any field SomeOldFieldName to MyNewFieldName. Run it, and then run the generated script.

Given this and CodeSmith, can you tell I enjoy scripting work?

Tip of the Day: More useful information on INFORMATION_SCHEMA

Thu, 11 Mar 2010 04:02:00 GMT

Recently, I posted a script which would identify all the procedures which did not have appropriate permissions set, which is great for last minute deployment checks. I realized I needed to explore INFORMATION_SCHEMA closer as there is a LOT of useful metadata about the database available. Today's tip is about some of that useful information.

INFORMATION_SCHEMA is a specia schema which contains several views of the database metadata. Previous to these views, database developers were forced to join on several sys tables. Many of my earlier scripts for example rely on this method. However, in SQL Server 2005 system views were provided which made this task significantly easier. Here's some of the more useful views to look at. Explore them with code like this and see what they contain.

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

I have found the following most useful, although in truth I suspect over time I will have use for all of them.

COLUMNS --> I frequently use this to find what tables a particular column name is found in

CONSTRAINT_COLUMN_USAGE --> identify what keys (primary, foreign) are defined, and on what fields

ROUTINES --> allows me to explore procedures and functions using this view

TABLES --> Get information about the various tables in the database

VIEW_COLUMN_USAGE --> obtain information on the fields the view contains

Here's a good link where you can find more information on the INFORMATION_SCHEMA.

Tip of the Day: Locating scripts without execute permissions set

Fri, 05 Mar 2010 04:36:29 GMT

Creating a script to grant execute to stored procedures where none exist

It’s very common to forget to script out permissions for a stored procedure when generating the script. There’s nothing more frustrating than deploying some code changes, only to have them not work because you have elevated security on your development SQL Server, but not on QA or Production. If you find one, likely you’ll find it several places. How do you quickly locate the procedures which don’t have the desired permission set? Better yet, how do you fix it quickly without writing a lot of script or manually setting the permissions in SQL Server Management Studio?

The script below is one I pulled off the internet (http://sqlfool.com/story/46/date-posted/06-06-0939/) and modified a little. Since the environment I am in now uses multiple schemas, I added the ability to specify schema to search for.

Here’s the script:

DECLARE @role VARCHAR(50)
DECLARE @targetSchema VARCHAR(50)
SET @targetSchema = 'dbo'
SET @role = 'MyUser'

SELECT 'GRANT EXECUTE ON ' + SPECIFIC_SCHEMA + '.[' + SPECIFIC_NAME + '] TO ' + @role + CHAR(10) + 'GO'
FROM INFORMATION_SCHEMA.ROUTINES WITH (NoLock)
WHERE SPECIFIC_NAME Not In
(SELECT o.name FROM sys.database_permissions p WITH (NoLock)
INNER Join sys.objects o WITH (NoLock)
ON p.major_id = o.OBJECT_ID
INNER Join sys.database_principals u WITH (NoLock)
ON u.principal_id = p.grantee_principal_id
WHERE u.name = @role)
AND ROUTINE_TYPE = 'PROCEDURE'
AND SPECIFIC_NAME not like 'sp_%'
AND SPECIFIC_SCHEMA = @targetSchema
ORDER BY SPECIFIC_NAME

Just change the @targetSchema and @role variables and run it. The results will be a list of procedures on the current database which don’t have execute permissions assigned to the indicated @role. You can then copy those procs you want to grant execute on into the query window and execute them.

Tip of the Day: SSIS -- adding carriage returns to annotations

Wed, 11 Nov 2009 17:13:49 GMT

It's been a LONG time since I did any SSIS work, and I really enjoy it. I'm writing an ETL process for importing general ledger transactions, and it's a fairly complex task. To help notate what needs to be done as well as explain some of the complexities, I am making sure to annotate the code in the Control Flow.

I had forgotten how to add carriage returns in annotations, so this is more a reminder again just in case I forget: Ctrl-Enter will do the trick!

Tip of the Day: Getting Table Counts

Wed, 02 Sep 2009 17:34:10 GMT

I can't take credit for this, as I took it from the article How To Get Table Row Counts Quickly And Painlessly on SqlServerCentral.com by Kendal Van Dyke. However, it such a good tip, I had to change it a bit, as I tend to use this a LOT.

First, I made the results a little more "printable" but reducing the table name column. Next, I made it a system proc because when I go onto client sites, I am looking at many databases. I probably don't need to create a procedure for it, but there have been many cases where I trend things, and it would be nice to have this handy. Finally, I added a parameter to allow me to change the sorting. By default, it sorts by table name. I added an optional parameter to allow me to pass in an integer (could have done it a million ways, I know, but I chose to do this solely for my preferences -- feel free to change to yours).

Usage: sp_retrieveRowCounts --- sorts by table name

sp_retrieveRowCounts 1 ---sorts by row count, then table name

Here's the proc:

USE master
GO

IF (SELECT OBJECT_ID('sp_retrieveRowCounts','P')) IS NOT NULL
BEGIN
    DROP PROC sp_retrieveRowCounts
END
GO

CREATE PROC sp_retrieveRowCounts (@OrderByCount int = null)
AS
BEGIN

    DECLARE @OrderByParm int
    DECLARE @SQLStatement varchar(700)
    DECLARE @OrderByClause varchar(35)

    SELECT @OrderByParm = ISNULL(@OrderByCount,0)

    IF (@OrderByParm != 0)
        SELECT @OrderByClause = ' ddps.row_count DESC, o.NAME ASC'
    ELSE
        SELECT @OrderByClause = ' o.NAME ASC'

    SELECT @SQLStatement = 'SELECT TableName = LEFT(o.name,50), TotalRows = ddps.row_count FROM sys.indexes AS i
     INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
     INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
     AND i.index_id = ddps.index_id
    WHERE i.index_id < 2
     AND o.is_ms_shipped = 0
    ORDER BY ' + @OrderByClause

    EXEC(@SQLStatement)

END

--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_retrieveRowCounts
GO

GRANT EXEC ON sp_retrieveRowCounts TO public
GO

Tip of the Day: A system procedure for locating fields in a database

Tue, 01 Sep 2009 04:10:15 GMT

When I go onto a client site, I usually don't have the luxury of designing everything from scratch. There are times (like on my current engagement) if I were given the choice, I'd say scrap it, but usually it doesn't happen that way. It is what it is... been saying that a lot lately!

So one of the tools I find useful is the ability to search for particular field names in the database. This is very similar to sp_findsp I use, which I have posted in the past off the net, but I have modified it so it gets registered as a system procedure.

So here's sp_findfields. It will return a fairly printable representation of the results of all fields which match the passed in string. It's registered as a system procedure in this script, so it is accessible to all databases.

Usage:

sp_findfields '%Contact%'

This will find all fields with the string 'Item' in it. Note you need to provide the wildcard characters.

USE master
GO

IF (SELECT OBJECT_ID('sp_findfields','P')) IS NOT NULL
    BEGIN
        DROP PROCEDURE sp_findfields
    END
GO

CREATE PROCEDURE sp_findfields
(@searchText varchar(50))
AS
BEGIN

    SELECT SchemaName = LEFT(TABLE_SCHEMA,10),
        TableName = LEFT(TABLE_NAME, 35),
        FieldName = LEFT(COLUMN_NAME, 40),
        DataType = LEFT(DATA_TYPE, 13),
        DataSize = CHARACTER_MAXIMUM_LENGTH,
        AlloysNulls = IS_NULLABLE
    FROM INFORMATION_SCHEMA.columns
    WHERE column_name like @searchText
    ORDER BY TableName, FieldName
END
GO

--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_findfields
GO

GRANT EXEC ON sp_findfields TO public
GO

Tip of the Day -- Determine current database name using T-SQL

Tue, 16 May 2006 15:38:30 GMT

Still working on getting posts over at developernation.net, and ran out of time on a series I am writing. This week looks to slow down a bit, so hopefully by the end of the week I can finish my series on a web service for paging and sorting data. Until then, I just ran across a situation where I needed to programmatically determine the current database name from T-SQL. Sounds easy, right? Well, fortunately it is, but the function to do it is documented poorly, so I figured I would post a tip so others could find it easily.

SELECT db_name()

The documentation says you need to pass in the db id, as does the example. However, if you read very closely, it appears you don't need to pass in the id, and it will return the current db for the connection. I wish they would have followed the documentation standard of using optional parameters with brackets [], and it would have saved some time.

A change of directions...

Fri, 28 Apr 2006 15:38:06 GMT

So it has been hit or miss posting here, but I am trying to correct it by bringing on some additional help. I set up a new blog, developernation.net, and begun getting some content on there. Likely I'll crosspost on here too (my content anyways) but likely most new entries are going to be there first. I figured if I could get someone else on there helping doing some content, it will have more regular posts. This blog will continue, just a lag a few days on the content of theother site, and contain more personal observations.

So swing on by and pop in. I've started off with a bang, and I am in the process of posting a web service design I created and tested which handles paging and sorting of data on the server, which my initial tests have shown to be highly efficient. I am walking through the entire design, and by the end of the weekend I hope to have the series done and the code posted. If not, you can wait a few days and I will post it all here as well.

Thanks!

Bizarre error, bizarre solution...

Tue, 20 Sep 2005 22:50:34 GMT

Once again I have disappeared from blogging. Let's face it, I rarely get to do it. I also need to upgrade this DasBlog but I have been so busy I haven't been able to do that, even though someone kindly wrote me and told me it would solve my spam comment problem. Maybe this weekend...

But I wanted to blog because I found a weird problem, with a weirder solution. I wrote a Windows service in C# which periodically loads some data, based on the time since the last load. So dates are a bit important in this process.

First, the cryptic message: “Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information”. A Google search turned up several solutions, but most of them relied on a service patch. What's wierder is that the error was intermittant. The procs would run fine in Query Analyzer, but not from C#.

Finally, I tried the unlikely. Instead of passing in my parameters as DateTime values, I passed them in as SqlDbType.Varchar, and in the proc made sure and converted them to DateTime values. Lo and behold, the problem went away.....Go figure!

Self Promotion

Thu, 26 May 2005 23:33:35 GMT

Tentatively, my current project is set to end in October. No big deal there, as I have had absolutely no bench days in the past 10 years as an Independent Contractor. In fact, during most of those 10 years I have been working 55-80+ hours per week and having a great time doing it. I don't do the book thing or magazine article thing because quite frankly, I don't have time because I am busy doing billable work which pays a lot more. I havn't needed self-promotion thing either because almost all my business comes from referrals from current or past customers.

Then along come kids, and dang if you don't suddenly want to spend more time at home and improve their quality of life (and mine). My current project wants to bring me on full time, and we've had some preliminary discussions on it, and I am willing to take the pay cut in my pay for one simple thing: I want to work remotely and raise my kids in the country. Their attitude is like mine, which is that should be fine since I have a strong work ethic and can get things done, what does it matter where I work as long as I have a phone and internet connection. However, there are data security concerns and management concerns which would need to be overcome.

I have plenty of time, since barring disaster, the contract won't be over until October sometime. But I figure I will start self-promoting just in case someone from a company who doesn't mind remote employees stumbles on this blog. Towards October, I will go full bore trying to close something out, and in the meantime I plan on blogging much more so I can display what talents I can bring to the table.

If any companies out there that come up on this, and want a great employee (C#/VB developer with lots of SQL Server and architect/designer/coding and full-life cycle development) that brings a lot of experience, a great professional attitude and presence as well as the ability to code like the wind (and good code at that..), then drop me a line and let's talk.

Here's a resume just in case..

Tip of the Day -- Using GetDate in functions

Fri, 11 Mar 2005 21:45:00 GMT

Someone asked me today if I knew that you couldn't use GETDATE() in functions in SQL Server. Well, I did, so I dug out this solution I had found a while back, and decided to post it, since I am sure others have come across the same issue.

Basically, you use a view to do the work for you... here you go!

CREATE VIEW Function_Assist_GETDATE

/********************************************************
*
* A view to return one row, with one column, the current
* date/time from the built-in function GETDATE(). This
* view allows a UDF to bypass the restriction on access to
* the non-deterministic getdate() function.
*
* Attribution: Based on a newsgroup posting in by Mikhail
* Berlyant in microsoft.public.sqlserver.programming
*
* Common Usage:
DECLARE @dtVar datetime
select @dtVAr = [GetDate] from Function_Assist_GETDATE
**********************************************************/

AS
SELECT getdate() as [GetDate]

GRANT SELECT on Function_Assist_GETDATE to PUBLIC
GO

The difference between @@IDENTITY, SCOPE_IDENTITY(), and IDENT_CURRENT()

Fri, 31 Dec 2004 15:25:39 GMT

So, you want to get the value of an identity column after an insert so you can update the UI or use it in another procedure. You've been using @@IDENTITY for ages, and it has worked fine. But today, you're getting a new value. What's up?

There are 3 (or more if you want to consider queries or identity tables) methods for getting the identity value from a table after an insert. The methods are @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(). What's the difference?

@@IDENTITY gets the last inserted identity value. The problem is, in a high-volumn environment or one with triggers, it may not be the value you are expecting.

The SCOPE_IDENTITY() function gets the last identity inserted within the same scope (e.g. inside the procedure you're running). This is useful especially when your table has triggers which might do another insert. You'll only get the identity value of your change, not the trigger's.

The IDENT_CURRENT('table_name') returns the last identity value for the specified table, regardless of scope.

So, for safety in your routines, if you want the value YOU inserted, try the SCOPE_IDENTITY function for safety!

Finding all tables where a field exists in SQL Server

Thu, 30 Dec 2004 18:09:49 GMT

Here's a quick and dirty routine I use all the time. This simple procedure will list all tables in your database where a particular field is found. Not earth shattering by any means, but useful and since I find I use it all the time, I thought someone else might find it userful and same them the brief effort to write their own!

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'sp_findField' AND type='P')
BEGIN
DROP PROCEDURE sp_findField
END
GO

CREATE PROCEDURE dbo.sp_findField (@fieldName varchar(100))
AS
BEGIN
SET NOCOUNT ON

SELECT so.name
FROM sysobjects so, syscolumns sc
WHERE so.id = sc.id
AND sc.name = @fieldName
ORDER BY so.name

END
GO

GRANT EXECUTE ON sp_findField TO public
GO

Tip of the Day: The Importance of Defragging your SQL Server drive

Sat, 11 Dec 2004 18:29:51 GMT

I am a huge fan of SQL Server. Don't get me wrong, there's nothing wrong with Oracle per se, other than it's tough to get it to run right and efficiently, and not for the amateur. If I was considering cross-platform work, Oracle would be the choice for me. For everything else, there's SQL Server, and when the next version comes out, look out!

But one “downfall” of SQL Server is that it becomes very easy to believe you can administer a production database with only Access skills, or worse, none at all. Some stories come to mind from my experiences.

First, I was doing a project for a large state agency, and at the time no one but the DBA could touch the SQL Server, or directly run script against the production server. I needed a very simple update script run, so I wrote it out, and gave it to the “DBA” to execute. He returns in about 5 minutes and tells me it doesn't run, and asked if I would mind taking a look at it. I did, and I had accidentally pasted two “set” keywords in a row. Even a Jr. Developer with a minutia of SQL experience should have seen this and been able to fix it. So, I fix it, and I asked him why he didn't just erase the extra “set”. is reply: I don't know SQL. Please keep it quiet.

Mind you, this guy was touted as a Sr. DBA, and responsible for about 16 databases which control some very important data. Unfortunately, this wasn't the worst of it.

One day I was using the restroom (that's important to the story), and he comes in and begins using the stall next to me. We begin some general chit-chat, and he's excited he can tell me something about SQL I might not know. His epiphany? “I went to a class, and we learned about indexes in it! I am so excited to start trying them out!” Well, since we had never been able to directly access the database, I had just solved some of our performance problems. I also got access to the databases from that day forward..

Another performance story. I was doing a project for an Internet startup, and I overheard they were having some SQL Server performance problems. The sign-ups for their site was killing their SQL Server with all the activity. I offered to take a look at it, but there was a definite anti-Microsoft spin at the company, and they were going to switch to Oracle because the owner had been told it was simply because Microsoft can't keep up with Oracle. I was working on a VB application for them, so they assumed wrongly that I didn't know databases very well.

So, they hire a team of Oracle (weren't employees of the Oracle Corp, just for reference) developers, and in 4 months and almost $300,000 later, it still wasn't up and running. The owner was bleeding cash, and was pressing the Oracle team to find out when the database conversion would be done and the bleeding would stop. They then informed him it would be about another month (and $100K), but even then they'd need to keep one of them one to maintain the database, or hire a full time Oracle DBA.

So I re-enter the SQL Server discussion with the owner. I ask him to let me look at the SQL Server configuration, and maybe I can buy him some time until the Oracle system is ready to go. Immediately, I see there are no indexes on the tables, and the stored procedures were very inefficient. By adding indexes alone, the performance gain alone went from ~98% resources used on the server (processor and memory) to less than 5% peak usage. The Oracle team was gone the next day (sorry guys), and the SQL Server portion ran flawlessly from that day on. But something so simple cost over $300K and wasted four months, and probably cost the company a lot of lost business.

My point is this: SQL Server is simple to use, but you have to educate yourself on how to use it best, because in some ways it makes it way too easy to think you are doing the right thing, when in fact it can have a huge impact on your performance. Next thing you know, you think the server can't handle it and you're out $20K on a new server which you don't need.

Case in point: I was hired by a bank to help a team of new developer's get up to speed in VB.Net and ASP.Net (I tried to get them to go C#, but that's another story). Their application was simple, but every day the resources on the server(s) were getting pegged and the entire system was unusable until the next reboot.

I decided looking into this would need to be a top priority. I checked the indexes, and they all looked good, but we did some tweaking there, and the problem was still occurring. I finally pull out the SQL Performance Analyzer, and quickly nail down there is a serious disk I/O problem. For example, I pull out a small table of about 200 rows, and do a select *, and it turns out it take 3000 disk I/O's to get it. The indexes were fine, and the record size was small, so there was something else going on.

On a hunch, I check to see if the disk drive is fragmented. Holy crap! It looked as if it had never been defragged, and come to find out, it hadn't. The network admin responsible for this production machine advised me the way he builds the machines means that they never have to be defragged. So I show him an image of the fragmentation on the drives, and he thinks I rigged it. End of conversation with him.

So I fix the problem, then ask for $200 for a program to schedule the de-fragmentation automatically. I don't get it, so I now have to manually defrag it once per week. Cost? Well, for me to track it down, and the time spent every week over a year and a half? Likely over $5000. Go figure.

I tried to get the employees there to take responsibility for doing it, but it never seemed to be a priority for them. So when I left to being a new project, I reminded them someone needed to do it. Well, I can assume for a while someone did. But I am back there, and yesterday there were complaints of poor performance. And guess what I found when I checked? The drives were very fragmented. One is so full and so bad, I think we're going to have to drop all the indexes, defragment, then rebuild them. That's gonna cost a ton of time and money. The $200 is going to be budgeted this time.

The tip: Make sure you keep your SQL Server data and index drives defragmented. Failure to do so can have a huge impact on your SQL Performance, since in the end, SQL Server relies on quick disk I/O to perform well.

-- Daryl

Creating a printable listing of fields in a table

Sat, 06 Nov 2004 16:45:22 GMT

On a rare occasion, I come across a circumstance where I can't create a SQL Server 2000 diagram, or the table is so huge and denormalized it doesn't really print from the diagram tool (which I love, BTW).

So, I ended up writing this little script which allows me to create a report which is easily printable. Yes, I am definitely a guy, as I am visually focused. :)

DECLARE @TableName VARCHAR(50)

SET @TableName = 'Authors'

select
'Table_Name'   = Left(sysobjects.name,25),
'Column_name'   = Left(syscolumns.name,25),
'Type'     = Left(type_name(xusertype),10),
'Length'    = convert(int, length),
'Nullable'    = case when isnullable = 0 then 'No' else 'Yes' end
from syscolumns, sysobjects where sysobjects.name = @TableName
and sysobjects.id = syscolumns.id
order by colid

Now, I imagine by now anyone reading these tips are saying “OK, nothing Earth shattering here..”. Give it time. Right now I am working on 2 projects, and it's hard to justify a lot of blogging time when I could be billing $XX per hour as many hours as I can stay awake and sane. At some point, I am going to write a series of articles on “Control Oriented Development”, and possibly give the keys to the city for a Questionaire engine I created which is basically limitless in how it presents questionaires to users. If you can think of a question, it can ask it... But that's down the road, so stick around!