February 4, 2014 at 11:30 pm
I am using powershell and the smo.scripter object to create scripts to create tables. About 10% of the tables that I have created using the smo scripter have moved columns to a different ordinal position than in the original table. Has anyone else experienced this, and even more important have a solution for it. In my task it is very important that I get create scripts for tables exactly as they are in the source database.
Thanks!
February 6, 2014 at 5:57 am
Please , could you provide more information about your problem ?
- 1 How do you know that the columns are not the same order ? Are you relying on the ORDINAL_POSITION of the INFORMATION_SCHEMA(COLUMNS) or on the column_id of the sys.columns ? These 2 values may be different if a column has been added , dropped or has changed its definition ( change the type from int to bigint ... ) needing an automatic recreation of the table in SSMS ( idem in case of inserting or dropping a column ).
- 2 Could you explain us what are the modifications brought to the schema ( add/drop/change type for a column ) ?
- I am not sure but I think that the change of NVARCHAR(2500) to NVARCHAR(MAX) provokes a change of the real position of the column ( if my remembrance is not too bad , the NVARCHAR(MAX) values are stored in "special" pages the address of which is stored in the same page as the other columns having a definition with a total length < 8000 )
A last thing : ORDINAL_POSITION is corresponding to the order the columns are listed in a SELECT * , it is not the same for column_id ( which is not changed when a column is dropped , creating gaps in the column_id values )
As I am not very good in English , I hope to have written in an understandable English and if it is not the case , I hope you will accept my excuses.
February 6, 2014 at 2:46 pm
Thanks for the response, I was unaware that the position from ordinal_position in information_schema could be different than the column_id in sys.columns. The column order matched between the two queries, but that got me looking a bit more and found the actual issue. We keep a model database for our main production database to do unit testing on before release change scripts. The issue is that we use SQL Compare to synchronize our databases in all environments. It ends up that if two columns are added during devleopment they can be added to the model in a different order than they are added to production. SQL Compare won't change the order of the columns; I assume this is to avoid having to rebuild a table during deployment which is a good thing. I was creating scripts from the model database to create tables for doing partition switches and that's where I ran into the problem of some of my tables not matching. In conclusion, smo.scripter does exactly what you would expect of it and crate the exact structure of the table being scripted.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply