Add column at ordinal position programmatically

Question

Add column at ordinal position programmatically

matthewspare

Old Hand

Points: 357
More actions
July 5, 2009 at 5:15 pm

#207863

I'm trying to create a procedure to effectively insert columns into specific places into a table that has data in it and has indexes. SQL Server 2000. Before you jump on me about ordinal positions should not matter, these tables can have hundreds of columns and the Data Techs (DTs) are using Enterprises Manager (EM) to directly manage these tables, and there are hundreds of these tables. The DT's look in certain places for certain columns, they are ordered logically by category. Right now the data techs edit the dev tables schema with EM and then edit the Production tables. When they are in a crunch they can make mistakes.
I need to look at the dev table and sync the schema (not data) in the Production environment to it programmatically. I've tried making the changes in EM and then scripting them, that has given me a good start.
How do I programmatically get the schema of the dev table with indexes
How do I programmatically insert records from the Orig Production table into the New Production table (with the new columns)
Any help will be much appreciated. If I posted this in the wrong area please straighten me out!
Thanks
Matthew

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Some aspects of this a relatively easy. But other aspects are really hard. And the hardest part is being sure that you haven't actually missed anything because there is like a hundred little things that rarely come up, but still sometimes do.

To answer your questions: syscolumns or INFORMATION_SCHEMA.COLUMNS has the columns for your table. sysindexes lists the indexes.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

matthewspare (7/5/2009)
...
How do I programmatically insert records from the Orig Production table into the New Production table (with the new columns)

You want to use SELECT INTO for this, as it takes car of a lot of the column datatype definition issues for you.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Lowell SSC Guru Points: 323504 More actions · Answer 3

How do I programmatically get the schema of the dev table with indexes
Any help will be much appreciated. If I posted this in the wrong area please straighten me out!
Thanks
Matthew

I slapped together a stored proc sp_GetDDL that returns the table DDL, complete with indexes ,defaults, everything...there's a function version as well; that would probably work just fine for you want to grab that info programmatically. it's a much more detailed version than a submission i did back in 2003.

I submitted it as an article already and eventually the article will hit the newsletters on it here on SSC;

see if this works for you:

sp_GetDDL for SQL 2005

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95409 More actions · Answer 4

Lowell (7/5/2009)
I slapped together a stored proc sp_GetDDL that returns the table DDL, complete with indexes ,defaults, everything...there's a function version as well; that would probably work just fine for you want to grab that info programmatically. it's a much more detailed version than a submission i did back in 2003.
I submitted it as an article already and eventually the article will hit the newsletters on it here on SSC;
see if this works for you:
sp_GetDDL for SQL 2005

Now this looks like a very interesting article. I'm already looking forward to seeing it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

matthewspare Old Hand Points: 357 More actions · Answer 5

Frickin awesome Lowell. This looks like what I'm trying to do. You are hurting me with the Cursor though. I read your use clause and will send you back the code with any helpful changes I make to it.

Thanks man!

Lowell SSC Guru Points: 323504 More actions · Answer 6

thanks matthew; I'm glad it looks like it might be useful for you.

it's something i play with when i have spare time; it started out before varchar(max) was available, but I've always thought when you are getting metadata, it's OK to use a cursor.

I'm trying to finish a version that uses the new INCLUDE syntax for indexes; I hope to get thatfinished in a week or two, when i can break loose some spare time to really test it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

WayneS (7/5/2009)
Lowell (7/5/2009)
I slapped together a stored proc sp_GetDDL that returns the table DDL, complete with indexes ,defaults, everything...there's a function version as well; that would probably work just fine for you want to grab that info programmatically. it's a much more detailed version than a submission i did back in 2003.
I submitted it as an article already and eventually the article will hit the newsletters on it here on SSC;
see if this works for you:
sp_GetDDL for SQL 2005
Now this looks like a very interesting article. I'm already looking forward to seeing it.

I have written similar code for building conversion views over staging tables and for building and altering tables in our ODS database based on staging tables; all without using cursors. Unfortunately the code is not accessable from home. If I get a chance I'll see if I can get it posted tomorrow during a break. I will have to sanatize the code some before posting it as there are some work specific entries I will need to delete or modify first.

matthewspare Old Hand Points: 357 More actions · Answer 8

Great! I didn't mention earlier, but we are using a dev to staging to production to post production work flow. All is wonderful until the client of an active project needs structural changes to their data. That is what this is in regards to. Syncing becomes a concern because we are pivoting tables between staging and production and post production. Yay.

Thanks for your help. I'm not a complete noob. I see this as a utility func/proc. I'll repost the scrubbed code. I know someone will find it useful. Please tell me where to post it.

Lowell SSC Guru Points: 323504 More actions · Answer 9

ok you got me thinking and in the mood, so i rewrote the procedure to get rid of all the cursors.

it's at least 5 times faster, cleaner code, blah blah... it's better, enough said i guess....

good to get my head out of my proverbial...uh..cursor.

give it a try and see what you think:

Improved no cursor Version 2 of sp_GetDDL for any table

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 10

OK as I'm testing this, I've found that the code I'm using for the PK/UQ/indexes, which uses sys.views instead of things like sysindexes, makes the proc work only if it is in the current database; the original proc could be dumped in master,and called from any db...

I'm not sure you can get the INCLUDE columns without using those views...I'm searching now to see if i can tweak it...for me it's important...one installation in master and i can use the proc for all databases.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

matthewspare Old Hand Points: 357 More actions · Answer 11

Thanks Lowell!

Your code is for SQL 2005. I'll try and retool it for SQL 2000 and send it back to you.

Matthew

Lowell SSC Guru Points: 323504 More actions · Answer 12

OK matthew; here's two versions for you, one for 2000, another for 2005;

as you'd guess, the 2000 version gets truncated if you have a huge table definition of 8000 or more chars, but otherwise it's working perfectly.

the code is MUCH simpler and easier to read than the old cursor monstrosity; thanks for making me look at it again.

the 2005 does not use any older compatibility views any longer, and if you are going to stick it in master, you need to mark it as a system stored procedure, so it works in all databases.

Get DDL for any SQL 2005 table

Get DDL for any SQL 2000 table

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 13

Steve Jones - SSC Editor

SSC Guru

Points: 734418

July 8, 2009 at 8:48 am

#1021730

Nice job, Lowell.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

RBarryYoung (7/5/2009)
Some aspects of this a relatively easy. But other aspects are really hard. And the hardest part is being sure that you haven't actually missed anything because there is like a hundred little things that rarely come up, but still sometimes do.
To answer your questions: syscolumns or INFORMATION_SCHEMA.COLUMNS has the columns for your table. sysindexes lists the indexes.

Right. What I should added here is that Lowell has already done almost all of this incredibly detailed and tedious work for us ... 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]