Create a table script with code

Question

Post reply

Create a table script with code

xinphinity

SSC Veteran

Points: 205
More actions
August 29, 2002 at 3:30 pm

#79019

I'm trying to create a script for a table object similar to what ent mgr creates when you script out a db. Anyone know an easy way to do this with T-SQL?
Edward Hunter
erhunter@comscore.com

Viewing 7 posts - 1 through 6 (of 6 total)

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

lbethel Old Hand Points: 371 More actions · Answer 1

You might want to start looking in sysobjects.

You can get table info from this:

select * from sysobjects where id = object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 2

You will want to build a variable that holds your TSQL command and then execute that command. Here are is a really simple examples:

declare @cmd char(100)

set @cmd = 'create table test (a char(1))'

exec (@cmd)

You might even want to look at the sp_executesql stored procedure documentation in Books online.

-------------------------

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

xinphinity SSC Veteran Points: 205 More actions · Answer 3

Much appreciated, but I am actually trying to get the CREATE TABLE script that ent mgr give you. I know there is a way to get it via code, I just havent found it yet.

Thanks for your reply!

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 4

Oh now I get it. If you start profiler and then you generate a script via EM you might get an idea how EM does it.

-------------------------

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

xinphinity SSC Veteran Points: 205 More actions · Answer 5

Gregory,

Thats sounds very promising. Any thoughts on how to config profiler to 'watch' the right area of SQL as it creates the script?

Thanks much!

Edward R. Hunter

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 6

Personally I would just run profile against my own personal copy of SQL Server, and take the default trace definitions. This should show you what EM does. Just to let you know I already did this and it shows TSQL commands like:

exec sp_MStablerefs ....

exec sp_MSdependencies ....

exec sp_MShelpcolumns ....

plus a number of select statements

So my suggested method will only show you the SQL calls EM used, but the actual data manipulations from these calls will still need to be determined by you.

Hope this help.....

If you need more information on how to reduce a profiler trace to just your login look in books online, and filter your trace on your login

-------------------------

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP