100 tables on a single Database

Question

100 tables on a single Database

sqlquery-101401

SSCarpal Tunnel

Points: 4951
More actions
June 9, 2005 at 6:33 am

#93193

I need to create 100 tables on a single Database, I want whenever I create a table it takes two cols (ID,NAME) automatically
can somebody help me out.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Create the table and then run an ALTER TABLE script on them. Maybe with sp_Msforeachtable or so.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

That would work... but why would you want to do that?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Did not get it. Does it mean sqlquery would like 2 same columns created each time he creates a table and all other columns are different and depend on particular table? How are the tables created? Code or EM? I would create a short script that in a loop creates a long script template that creates 100 tables with those 2 fields. The I would fill out the rest: table names, other fileds. Something like:

Set NoCount ON

Declare @i int

Select @i=1

While @i < 101

Begin

Select 'Create Table

(ID int, Name varchar(30))'

Select @i = @i+1

End

The output will be 100 Create Table statements without table names but with 2 same fields. If the table names are stored somewhere then it is even easier. Suppose we take table names from sysobjects from some database:

Select 'Create Table ' + Substring(name,1,30) +

' (ID int, Name varchar(30))'

from sysobjects

where xtype ='U'

Yelena

Regards,Yelena Varsha

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Yes, my understanding is, that every time a new table is created those two columns are automatically added. Looks like some kind of auditing information to me.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Maybe, but untill he tells us why he wants to do that, it's hard to help him.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 6

If he needs to create tables one at a time, not a butch of 100 (see my previous post for a butch) then could he just create a table, stuff it with what he needs, call it ModelTable, script it for QA model query or in EM just click on this table design every time he needs to add a table and save this design under a new name?

Yelena

Regards,Yelena Varsha

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Would work nicely... but the same problem remains : untill we know why he wants to do this, we're simply guessing solutions.

Jeff Gray SSChampion Points: 10667 More actions · Answer 8

To answer his question I would just like to know what he wants to do. If he's interested in further help, he could tell us why.

So...OP, Can you restate the question in more detail? It sounds like you want to have two columns automatically added to any table that is created. I doubt if it is possible to happen *instantly* but a scheduled job could be created that looks for tables that don't have the columns and then add them.

Might not be the best thing to do, but then again, it might just be.

hth

jg

sqlquery-101401 SSCarpal Tunnel Points: 4951 More actions · Answer 9

sorry friends i could not reply to the thread as i was out for quite some time,but yes the question was same when i create the table columns get created automatically.

thanks all.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Looks like you're gonna have to go with the jobs option. Because there's no way to have them added automatically, unless again, you save a default script and then work with that to finish the work.