Is there a special sp on SQL server 2008 to get a table change script?

Question

Hi there,

I wonder how management studio generates the change table scripts each time i change a table, especially when i change the datatype from 'text' to 'varchar(max)'.

Peace, Ice

Update: Concerning the already given Answers i want to precise my question, after say thank you for your posts. Now, it is like already said, ssms generates the scripts on demand but i want to write a script to identify all the tables in a given database with datatype [text] and change them to [varchar(max)]. So the easy part is to scan the dictionary to find the tables and columns, the harder part is to generate the mentioned scripts which copies all the rows into the new structur. --> Yes i can work manually thru the list of tables and click in ssms to get the scripts, but there are almost 200 tables... better a computer do that work, isn't it?

Answer 1

When you change a table using the Management Studio table designer, the script is already prepared in the background, and you can simply click on the "Generate change script" icon to view it (and copy it if you like).

In SQL 2008, the "Generate change script" option is in the "Table Designer" menu.

If you're asking what it does, it's quite simple: SQL will create a new table with the new structure, copy all the data from the old table, drop the old one and rename the new one. If you open the script as described above, you'd see that.

Answer 2

If what you're asking is "Given a table, is there an easy way to get the DML used to create that table?" then I don't believe you can just run a system SP and get it. You'd need write something that selected from the system tables to get columns and datatypes, indexes, and everything else.

I wish there was an easy way to generate the same scripts SSMS does (like right-click -> "Modify" on a stored procedure), but it doesn't seem to be that easy.