Link to home
Start Free TrialLog in
Avatar of VeeVan
VeeVan

asked on

Use SQL Script to create table in multiple databases upon install with Installshield.

I have a commercial program for which I am writing a new module. It uses installshield to install updates to the program. I need to create several new tables for program. Each installation of the program is going to use the same SQL instance, however, they can have multiple databases (all with identical tables) and the names are unknown (on my end -- they create them themselves.)

I need to use a SQL script to create new tables and populate them in each one of the databases taht exists for the SQL instance, and have no idea how to go about this.

(I know how to write the generic SQL create script, just not how to automatically apply it to each database.)

Thanks in advance.
Vee
Avatar of Aneesh
Aneesh
Flag of Canada image
> automatically apply it to each database.
you cna use 'Osql.exe' to apply the script. check the OSQL to see different options
Avatar of VeeVan
VeeVan

ASKER

so would i use a batch file to run the .sql file using osql? and can you give me a rough syntax on what the command line would be?

Please and thank you.
Avatar of VeeVan
VeeVan

ASKER

Also, is there a way to exclude the system databases using this?
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aneesh
Aneesh
Flag of Canada image
Do you need to run this on all the dtabases ?
Avatar of VeeVan
VeeVan

ASKER

yes, it needs to run on all databases, except the system DB's.  I know the names of the system DB's, I don't know the name of the remaining db.'s

If necessary, I can create the tables on all DB's and then drop them on the system db's. (not the preferred methodology, however.)
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
EXEC sp_msforeachdb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''replication'', ''tempdb'')
BEGIN
    PRINT ''?''
    --...script to create table(s), etc. here    
END
'
Avatar of VeeVan
VeeVan

ASKER

OK. Here is the completed script: I am getting an error when I try to run through osql:
The error is: "Implicit conversion from datatype text to nvarchar is not allowed. Use the CONVERT funtion to run this query."

I have tried any number of derivations on the quotes, but can't seem to get it to work. Any assistance is greatly appreciated.

Thanks.
V

======================================================================

EXEC sp_msforeachdb '
IF  ? not in (''master'', ''model'', ''msdb'', ''replication'', ''tempdb'')
begin

if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[LSCUSTOM]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
drop table [dbo].[LSCUSTOM]
END

CREATE TABLE [dbo].[LSCUSTOM](
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
      [Text1] [nvarchar](50) NULL,
      [Text2] [nvarchar](50) NULL,
      [Text3] [nvarchar](50) NULL,
      [Text4] [nvarchar](50) NULL,
      [Text5] [nvarchar](50) NULL,
      [Text6] [nvarchar](50) NULL,
      [Text7] [nvarchar](50) NULL,
      [Text8] [nvarchar](50) NULL,
      [Text9] [nvarchar](50) NULL,
      [Text10] [nvarchar](50) NULL,
      [Text11] [nvarchar](50) NULL,
      [Text12] [nvarchar](50) NULL,
      [Text13] [nvarchar](50) NULL,
      [Text14] [nvarchar](50) NULL,
      [Text15] [nvarchar](50) NULL,
      [Text16] [nvarchar](50) NULL,
      [Text17] [nvarchar](50) NULL,
      [Text18] [nvarchar](50) NULL,
      [Text19] [nvarchar](50) NULL,
      [Text20] [nvarchar](50) NULL,
      [Text21] [nvarchar](50) NULL,
      [Text22] [nvarchar](50) NULL,
      [Text23] [nvarchar](50) NULL,
      [Text24] [nvarchar](50) NULL,
      [Text25] [nvarchar](50) NULL,
      [Text26] [nvarchar](50) NULL,
      [Text27] [nvarchar](50) NULL,
      [Text28] [nvarchar](50) NULL,
      [Text29] [nvarchar](50) NULL,
      [Text30] [nvarchar](50) NULL,
      [Text31] [nvarchar](50) NULL,
      [Text32] [nvarchar](50) NULL,
      [Text33] [nvarchar](50) NULL,
      [Text34] [nvarchar](50) NULL,
      [Text35] [nvarchar](50) NULL,
      [Text36] [nvarchar](50) NULL,
      [Text37] [nvarchar](50) NULL,
      [Text38] [nvarchar](50) NULL,
      [Text39] [nvarchar](50) NULL,
      [Text40] [nvarchar](50) NULL,
      [Text41] [nvarchar](50) NULL,
      [Text42] [nvarchar](50) NULL,
      [Text43] [nvarchar](50) NULL,
      [Text44] [nvarchar](50) NULL,
      [Text45] [nvarchar](50) NULL,
      [Text46] [nvarchar](50) NULL,
      [Text47] [nvarchar](50) NULL,
      [Text48] [nvarchar](50) NULL,
      [Text49] [nvarchar](50) NULL,
      [Text50] [nvarchar](50) NULL,
      [Date1] [datetime] NULL,
      [Date2] [datetime] NULL,
      [Date3] [datetime] NULL,
      [Date4] [datetime] NULL,
      [Date5] [datetime] NULL,
      [Date6] [datetime] NULL,
      [Date7] [datetime] NULL,
      [Date8] [datetime] NULL,
      [Date9] [datetime] NULL,
      [Date10] [datetime] NULL,
      [Date11] [datetime] NULL,
      [Date12] [datetime] NULL,
      [Date13] [datetime] NULL,
      [Date14] [datetime] NULL,
      [Date15] [datetime] NULL,
      [Date16] [datetime] NULL,
      [Date17] [datetime] NULL,
      [Date18] [datetime] NULL,
      [Date19] [datetime] NULL,
      [Date20] [datetime] NULL,
      [Date21] [datetime] NULL,
      [Date22] [datetime] NULL,
      [Date23] [datetime] NULL,
      [Date24] [datetime] NULL,
      [Date25] [datetime] NULL,
      [Number1] [float] NULL,
      [Number2] [float] NULL,
      [Number3] [float] NULL,
      [Number4] [float] NULL,
      [Number5] [float] NULL,
      [Number6] [float] NULL,
      [Number7] [float] NULL,
      [Number8] [float] NULL,
      [Number9] [float] NULL,
      [Number10] [float] NULL,
      [Number11] [float] NULL,
      [Number12] [float] NULL,
      [Number13] [float] NULL,
      [Number14] [float] NULL,
      [Number15] [float] NULL,
      [Number16] [float] NULL,
      [Number17] [float] NULL,
      [Number18] [float] NULL,
      [Number19] [float] NULL,
      [Number20] [float] NULL,
      [Number21] [float] NULL,
      [Number22] [float] NULL,
      [Number23] [float] NULL,
      [Number24] [float] NULL,
      [Number25] [float] NULL,
      [Memo1] [text] NULL,
      [Memo2] [text] NULL,
      [Memo3] [text] NULL,
      [Memo4] [text] NULL,
      [Memo5] [text] NULL,
      [Memo6] [text] NULL,
      [Memo7] [text] NULL,
      [Memo8] [text] NULL,
      [Memo9] [text] NULL,
      [Memo10] [text] NULL,
 CONSTRAINT [PK__LSCUSTOM__68487DD7] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[IPCUSTOM]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
drop table [dbo].[IPCUSTOM]
END


CREATE TABLE [dbo].[IPCUSTOM](
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
      [Text1] [nvarchar](50) NULL,
      [Text2] [nvarchar](50) NULL,
      [Text3] [nvarchar](50) NULL,
      [Text4] [nvarchar](50) NULL,
      [Text5] [nvarchar](50) NULL,
      [Text6] [nvarchar](50) NULL,
      [Text7] [nvarchar](50) NULL,
      [Text8] [nvarchar](50) NULL,
      [Text9] [nvarchar](50) NULL,
      [Text10] [nvarchar](50) NULL,
      [Text11] [nvarchar](50) NULL,
      [Text12] [nvarchar](50) NULL,
      [Text13] [nvarchar](50) NULL,
      [Text14] [nvarchar](50) NULL,
      [Text15] [nvarchar](50) NULL,
      [Text16] [nvarchar](50) NULL,
      [Text17] [nvarchar](50) NULL,
      [Text18] [nvarchar](50) NULL,
      [Text19] [nvarchar](50) NULL,
      [Text20] [nvarchar](50) NULL,
      [Text21] [nvarchar](50) NULL,
      [Text22] [nvarchar](50) NULL,
      [Text23] [nvarchar](50) NULL,
      [Text24] [nvarchar](50) NULL,
      [Text25] [nvarchar](50) NULL,
      [Text26] [nvarchar](50) NULL,
      [Text27] [nvarchar](50) NULL,
      [Text28] [nvarchar](50) NULL,
      [Text29] [nvarchar](50) NULL,
      [Text30] [nvarchar](50) NULL,
      [Text31] [nvarchar](50) NULL,
      [Text32] [nvarchar](50) NULL,
      [Text33] [nvarchar](50) NULL,
      [Text34] [nvarchar](50) NULL,
      [Text35] [nvarchar](50) NULL,
      [Text36] [nvarchar](50) NULL,
      [Text37] [nvarchar](50) NULL,
      [Text38] [nvarchar](50) NULL,
      [Text39] [nvarchar](50) NULL,
      [Text40] [nvarchar](50) NULL,
      [Text41] [nvarchar](50) NULL,
      [Text42] [nvarchar](50) NULL,
      [Text43] [nvarchar](50) NULL,
      [Text44] [nvarchar](50) NULL,
      [Text45] [nvarchar](50) NULL,
      [Text46] [nvarchar](50) NULL,
      [Text47] [nvarchar](50) NULL,
      [Text48] [nvarchar](50) NULL,
      [Text49] [nvarchar](50) NULL,
      [Text50] [nvarchar](50) NULL,
      [Date1] [datetime] NULL,
      [Date2] [datetime] NULL,
      [Date3] [datetime] NULL,
      [Date4] [datetime] NULL,
      [Date5] [datetime] NULL,
      [Date6] [datetime] NULL,
      [Date7] [datetime] NULL,
      [Date8] [datetime] NULL,
      [Date9] [datetime] NULL,
      [Date10] [datetime] NULL,
      [Date11] [datetime] NULL,
      [Date12] [datetime] NULL,
      [Date13] [datetime] NULL,
      [Date14] [datetime] NULL,
      [Date15] [datetime] NULL,
      [Date16] [datetime] NULL,
      [Date17] [datetime] NULL,
      [Date18] [datetime] NULL,
      [Date19] [datetime] NULL,
      [Date20] [datetime] NULL,
      [Date21] [datetime] NULL,
      [Date22] [datetime] NULL,
      [Date23] [datetime] NULL,
      [Date24] [datetime] NULL,
      [Date25] [datetime] NULL,
      [Number1] [float] NULL,
      [Number2] [float] NULL,
      [Number3] [float] NULL,
      [Number4] [float] NULL,
      [Number5] [float] NULL,
      [Number6] [float] NULL,
      [Number7] [float] NULL,
      [Number8] [float] NULL,
      [Number9] [float] NULL,
      [Number10] [float] NULL,
      [Number11] [float] NULL,
      [Number12] [float] NULL,
      [Number13] [float] NULL,
      [Number14] [float] NULL,
      [Number15] [float] NULL,
      [Number16] [float] NULL,
      [Number17] [float] NULL,
      [Number18] [float] NULL,
      [Number19] [float] NULL,
      [Number20] [float] NULL,
      [Number21] [float] NULL,
      [Number22] [float] NULL,
      [Number23] [float] NULL,
      [Number24] [float] NULL,
      [Number25] [float] NULL,
      [Memo1] [text] NULL,
      [Memo2] [text] NULL,
      [Memo3] [text] NULL,
      [Memo4] [text] NULL,
      [Memo5] [text] NULL,
      [Memo6] [text] NULL,
      [Memo7] [text] NULL,
      [Memo8] [text] NULL,
      [Memo9] [text] NULL,
      [Memo10] [text] NULL,
 CONSTRAINT [PK__IPCUSTOM__68487DD7] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[HMCUSTOM]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
drop table [dbo].[HMCUSTOM]
END


CREATE TABLE [dbo].[HMCUSTOM](
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
      [Text1] [nvarchar](50) NULL,
      [Text2] [nvarchar](50) NULL,
      [Text3] [nvarchar](50) NULL,
      [Text4] [nvarchar](50) NULL,
      [Text5] [nvarchar](50) NULL,
      [Text6] [nvarchar](50) NULL,
      [Text7] [nvarchar](50) NULL,
      [Text8] [nvarchar](50) NULL,
      [Text9] [nvarchar](50) NULL,
      [Text10] [nvarchar](50) NULL,
      [Text11] [nvarchar](50) NULL,
      [Text12] [nvarchar](50) NULL,
      [Text13] [nvarchar](50) NULL,
      [Text14] [nvarchar](50) NULL,
      [Text15] [nvarchar](50) NULL,
      [Text16] [nvarchar](50) NULL,
      [Text17] [nvarchar](50) NULL,
      [Text18] [nvarchar](50) NULL,
      [Text19] [nvarchar](50) NULL,
      [Text20] [nvarchar](50) NULL,
      [Text21] [nvarchar](50) NULL,
      [Text22] [nvarchar](50) NULL,
      [Text23] [nvarchar](50) NULL,
      [Text24] [nvarchar](50) NULL,
      [Text25] [nvarchar](50) NULL,
      [Text26] [nvarchar](50) NULL,
      [Text27] [nvarchar](50) NULL,
      [Text28] [nvarchar](50) NULL,
      [Text29] [nvarchar](50) NULL,
      [Text30] [nvarchar](50) NULL,
      [Text31] [nvarchar](50) NULL,
      [Text32] [nvarchar](50) NULL,
      [Text33] [nvarchar](50) NULL,
      [Text34] [nvarchar](50) NULL,
      [Text35] [nvarchar](50) NULL,
      [Text36] [nvarchar](50) NULL,
      [Text37] [nvarchar](50) NULL,
      [Text38] [nvarchar](50) NULL,
      [Text39] [nvarchar](50) NULL,
      [Text40] [nvarchar](50) NULL,
      [Text41] [nvarchar](50) NULL,
      [Text42] [nvarchar](50) NULL,
      [Text43] [nvarchar](50) NULL,
      [Text44] [nvarchar](50) NULL,
      [Text45] [nvarchar](50) NULL,
      [Text46] [nvarchar](50) NULL,
      [Text47] [nvarchar](50) NULL,
      [Text48] [nvarchar](50) NULL,
      [Text49] [nvarchar](50) NULL,
      [Text50] [nvarchar](50) NULL,
      [Date1] [datetime] NULL,
      [Date2] [datetime] NULL,
      [Date3] [datetime] NULL,
      [Date4] [datetime] NULL,
      [Date5] [datetime] NULL,
      [Date6] [datetime] NULL,
      [Date7] [datetime] NULL,
      [Date8] [datetime] NULL,
      [Date9] [datetime] NULL,
      [Date10] [datetime] NULL,
      [Date11] [datetime] NULL,
      [Date12] [datetime] NULL,
      [Date13] [datetime] NULL,
      [Date14] [datetime] NULL,
      [Date15] [datetime] NULL,
      [Date16] [datetime] NULL,
      [Date17] [datetime] NULL,
      [Date18] [datetime] NULL,
      [Date19] [datetime] NULL,
      [Date20] [datetime] NULL,
      [Date21] [datetime] NULL,
      [Date22] [datetime] NULL,
      [Date23] [datetime] NULL,
      [Date24] [datetime] NULL,
      [Date25] [datetime] NULL,
      [Number1] [float] NULL,
      [Number2] [float] NULL,
      [Number3] [float] NULL,
      [Number4] [float] NULL,
      [Number5] [float] NULL,
      [Number6] [float] NULL,
      [Number7] [float] NULL,
      [Number8] [float] NULL,
      [Number9] [float] NULL,
      [Number10] [float] NULL,
      [Number11] [float] NULL,
      [Number12] [float] NULL,
      [Number13] [float] NULL,
      [Number14] [float] NULL,
      [Number15] [float] NULL,
      [Number16] [float] NULL,
      [Number17] [float] NULL,
      [Number18] [float] NULL,
      [Number19] [float] NULL,
      [Number20] [float] NULL,
      [Number21] [float] NULL,
      [Number22] [float] NULL,
      [Number23] [float] NULL,
      [Number24] [float] NULL,
      [Number25] [float] NULL,
      [Memo1] [text] NULL,
      [Memo2] [text] NULL,
      [Memo3] [text] NULL,
      [Memo4] [text] NULL,
      [Memo5] [text] NULL,
      [Memo6] [text] NULL,
      [Memo7] [text] NULL,
      [Memo8] [text] NULL,
      [Memo9] [text] NULL,
      [Memo10] [text] NULL,
 CONSTRAINT [PK__HMCUSTOM__68487DD7] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[APCUSTOM]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
drop table [dbo].[APCUSTOM]
END


CREATE TABLE [dbo].[APCUSTOM](
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
      [Text1] [nvarchar](50) NULL,
      [Text2] [nvarchar](50) NULL,
      [Text3] [nvarchar](50) NULL,
      [Text4] [nvarchar](50) NULL,
      [Text5] [nvarchar](50) NULL,
      [Text6] [nvarchar](50) NULL,
      [Text7] [nvarchar](50) NULL,
      [Text8] [nvarchar](50) NULL,
      [Text9] [nvarchar](50) NULL,
      [Text10] [nvarchar](50) NULL,
      [Text11] [nvarchar](50) NULL,
      [Text12] [nvarchar](50) NULL,
      [Text13] [nvarchar](50) NULL,
      [Text14] [nvarchar](50) NULL,
      [Text15] [nvarchar](50) NULL,
      [Text16] [nvarchar](50) NULL,
      [Text17] [nvarchar](50) NULL,
      [Text18] [nvarchar](50) NULL,
      [Text19] [nvarchar](50) NULL,
      [Text20] [nvarchar](50) NULL,
      [Text21] [nvarchar](50) NULL,
      [Text22] [nvarchar](50) NULL,
      [Text23] [nvarchar](50) NULL,
      [Text24] [nvarchar](50) NULL,
      [Text25] [nvarchar](50) NULL,
      [Text26] [nvarchar](50) NULL,
      [Text27] [nvarchar](50) NULL,
      [Text28] [nvarchar](50) NULL,
      [Text29] [nvarchar](50) NULL,
      [Text30] [nvarchar](50) NULL,
      [Text31] [nvarchar](50) NULL,
      [Text32] [nvarchar](50) NULL,
      [Text33] [nvarchar](50) NULL,
      [Text34] [nvarchar](50) NULL,
      [Text35] [nvarchar](50) NULL,
      [Text36] [nvarchar](50) NULL,
      [Text37] [nvarchar](50) NULL,
      [Text38] [nvarchar](50) NULL,
      [Text39] [nvarchar](50) NULL,
      [Text40] [nvarchar](50) NULL,
      [Text41] [nvarchar](50) NULL,
      [Text42] [nvarchar](50) NULL,
      [Text43] [nvarchar](50) NULL,
      [Text44] [nvarchar](50) NULL,
      [Text45] [nvarchar](50) NULL,
      [Text46] [nvarchar](50) NULL,
      [Text47] [nvarchar](50) NULL,
      [Text48] [nvarchar](50) NULL,
      [Text49] [nvarchar](50) NULL,
      [Text50] [nvarchar](50) NULL,
      [Date1] [datetime] NULL,
      [Date2] [datetime] NULL,
      [Date3] [datetime] NULL,
      [Date4] [datetime] NULL,
      [Date5] [datetime] NULL,
      [Date6] [datetime] NULL,
      [Date7] [datetime] NULL,
      [Date8] [datetime] NULL,
      [Date9] [datetime] NULL,
      [Date10] [datetime] NULL,
      [Date11] [datetime] NULL,
      [Date12] [datetime] NULL,
      [Date13] [datetime] NULL,
      [Date14] [datetime] NULL,
      [Date15] [datetime] NULL,
      [Date16] [datetime] NULL,
      [Date17] [datetime] NULL,
      [Date18] [datetime] NULL,
      [Date19] [datetime] NULL,
      [Date20] [datetime] NULL,
      [Date21] [datetime] NULL,
      [Date22] [datetime] NULL,
      [Date23] [datetime] NULL,
      [Date24] [datetime] NULL,
      [Date25] [datetime] NULL,
      [Number1] [float] NULL,
      [Number2] [float] NULL,
      [Number3] [float] NULL,
      [Number4] [float] NULL,
      [Number5] [float] NULL,
      [Number6] [float] NULL,
      [Number7] [float] NULL,
      [Number8] [float] NULL,
      [Number9] [float] NULL,
      [Number10] [float] NULL,
      [Number11] [float] NULL,
      [Number12] [float] NULL,
      [Number13] [float] NULL,
      [Number14] [float] NULL,
      [Number15] [float] NULL,
      [Number16] [float] NULL,
      [Number17] [float] NULL,
      [Number18] [float] NULL,
      [Number19] [float] NULL,
      [Number20] [float] NULL,
      [Number21] [float] NULL,
      [Number22] [float] NULL,
      [Number23] [float] NULL,
      [Number24] [float] NULL,
      [Number25] [float] NULL,
      [Memo1] [text] NULL,
      [Memo2] [text] NULL,
      [Memo3] [text] NULL,
      [Memo4] [text] NULL,
      [Memo5] [text] NULL,
      [Memo6] [text] NULL,
      [Memo7] [text] NULL,
      [Memo8] [text] NULL,
      [Memo9] [text] NULL,
      [Memo10] [text] NULL,
 CONSTRAINT [PK__APCUSTOM__68487DD7] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[RTCUSTOM]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
drop table [dbo].[RTCUSTOM]
END

CREATE TABLE [dbo].[RTCUSTOM](
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
      [Text1] [nvarchar](50) NULL,
      [Text2] [nvarchar](50) NULL,
      [Text3] [nvarchar](50) NULL,
      [Text4] [nvarchar](50) NULL,
      [Text5] [nvarchar](50) NULL,
      [Text6] [nvarchar](50) NULL,
      [Text7] [nvarchar](50) NULL,
      [Text8] [nvarchar](50) NULL,
      [Text9] [nvarchar](50) NULL,
      [Text10] [nvarchar](50) NULL,
      [Text11] [nvarchar](50) NULL,
      [Text12] [nvarchar](50) NULL,
      [Text13] [nvarchar](50) NULL,
      [Text14] [nvarchar](50) NULL,
      [Text15] [nvarchar](50) NULL,
      [Text16] [nvarchar](50) NULL,
      [Text17] [nvarchar](50) NULL,
      [Text18] [nvarchar](50) NULL,
      [Text19] [nvarchar](50) NULL,
      [Text20] [nvarchar](50) NULL,
      [Text21] [nvarchar](50) NULL,
      [Text22] [nvarchar](50) NULL,
      [Text23] [nvarchar](50) NULL,
      [Text24] [nvarchar](50) NULL,
      [Text25] [nvarchar](50) NULL,
      [Text26] [nvarchar](50) NULL,
      [Text27] [nvarchar](50) NULL,
      [Text28] [nvarchar](50) NULL,
      [Text29] [nvarchar](50) NULL,
      [Text30] [nvarchar](50) NULL,
      [Text31] [nvarchar](50) NULL,
      [Text32] [nvarchar](50) NULL,
      [Text33] [nvarchar](50) NULL,
      [Text34] [nvarchar](50) NULL,
      [Text35] [nvarchar](50) NULL,
      [Text36] [nvarchar](50) NULL,
      [Text37] [nvarchar](50) NULL,
      [Text38] [nvarchar](50) NULL,
      [Text39] [nvarchar](50) NULL,
      [Text40] [nvarchar](50) NULL,
      [Text41] [nvarchar](50) NULL,
      [Text42] [nvarchar](50) NULL,
      [Text43] [nvarchar](50) NULL,
      [Text44] [nvarchar](50) NULL,
      [Text45] [nvarchar](50) NULL,
      [Text46] [nvarchar](50) NULL,
      [Text47] [nvarchar](50) NULL,
      [Text48] [nvarchar](50) NULL,
      [Text49] [nvarchar](50) NULL,
      [Text50] [nvarchar](50) NULL,
      [Date1] [datetime] NULL,
      [Date2] [datetime] NULL,
      [Date3] [datetime] NULL,
      [Date4] [datetime] NULL,
      [Date5] [datetime] NULL,
      [Date6] [datetime] NULL,
      [Date7] [datetime] NULL,
      [Date8] [datetime] NULL,
      [Date9] [datetime] NULL,
      [Date10] [datetime] NULL,
      [Date11] [datetime] NULL,
      [Date12] [datetime] NULL,
      [Date13] [datetime] NULL,
      [Date14] [datetime] NULL,
      [Date15] [datetime] NULL,
      [Date16] [datetime] NULL,
      [Date17] [datetime] NULL,
      [Date18] [datetime] NULL,
      [Date19] [datetime] NULL,
      [Date20] [datetime] NULL,
      [Date21] [datetime] NULL,
      [Date22] [datetime] NULL,
      [Date23] [datetime] NULL,
      [Date24] [datetime] NULL,
      [Date25] [datetime] NULL,
      [Number1] [float] NULL,
      [Number2] [float] NULL,
      [Number3] [float] NULL,
      [Number4] [float] NULL,
      [Number5] [float] NULL,
      [Number6] [float] NULL,
      [Number7] [float] NULL,
      [Number8] [float] NULL,
      [Number9] [float] NULL,
      [Number10] [float] NULL,
      [Number11] [float] NULL,
      [Number12] [float] NULL,
      [Number13] [float] NULL,
      [Number14] [float] NULL,
      [Number15] [float] NULL,
      [Number16] [float] NULL,
      [Number17] [float] NULL,
      [Number18] [float] NULL,
      [Number19] [float] NULL,
      [Number20] [float] NULL,
      [Number21] [float] NULL,
      [Number22] [float] NULL,
      [Number23] [float] NULL,
      [Number24] [float] NULL,
      [Number25] [float] NULL,
      [Memo1] [text] NULL,
      [Memo2] [text] NULL,
      [Memo3] [text] NULL,
      [Memo4] [text] NULL,
      [Memo5] [text] NULL,
      [Memo6] [text] NULL,
      [Memo7] [text] NULL,
      [Memo8] [text] NULL,
      [Memo9] [text] NULL,
      [Memo10] [text] NULL,
 CONSTRAINT [PK__RTCUSTOM__68487DD7] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[APRCUSTOM]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
BEGIN
drop table [dbo].[APRCUSTOM]
END


CREATE TABLE [dbo].[APRCUSTOM](
      [RecordNo] [int] IDENTITY(1,1) NOT NULL,
      [Text1] [nvarchar](50) NULL,
      [Text2] [nvarchar](50) NULL,
      [Text3] [nvarchar](50) NULL,
      [Text4] [nvarchar](50) NULL,
      [Text5] [nvarchar](50) NULL,
      [Text6] [nvarchar](50) NULL,
      [Text7] [nvarchar](50) NULL,
      [Text8] [nvarchar](50) NULL,
      [Text9] [nvarchar](50) NULL,
      [Text10] [nvarchar](50) NULL,
      [Text11] [nvarchar](50) NULL,
      [Text12] [nvarchar](50) NULL,
      [Text13] [nvarchar](50) NULL,
      [Text14] [nvarchar](50) NULL,
      [Text15] [nvarchar](50) NULL,
      [Text16] [nvarchar](50) NULL,
      [Text17] [nvarchar](50) NULL,
      [Text18] [nvarchar](50) NULL,
      [Text19] [nvarchar](50) NULL,
      [Text20] [nvarchar](50) NULL,
      [Text21] [nvarchar](50) NULL,
      [Text22] [nvarchar](50) NULL,
      [Text23] [nvarchar](50) NULL,
      [Text24] [nvarchar](50) NULL,
      [Text25] [nvarchar](50) NULL,
      [Text26] [nvarchar](50) NULL,
      [Text27] [nvarchar](50) NULL,
      [Text28] [nvarchar](50) NULL,
      [Text29] [nvarchar](50) NULL,
      [Text30] [nvarchar](50) NULL,
      [Text31] [nvarchar](50) NULL,
      [Text32] [nvarchar](50) NULL,
      [Text33] [nvarchar](50) NULL,
      [Text34] [nvarchar](50) NULL,
      [Text35] [nvarchar](50) NULL,
      [Text36] [nvarchar](50) NULL,
      [Text37] [nvarchar](50) NULL,
      [Text38] [nvarchar](50) NULL,
      [Text39] [nvarchar](50) NULL,
      [Text40] [nvarchar](50) NULL,
      [Text41] [nvarchar](50) NULL,
      [Text42] [nvarchar](50) NULL,
      [Text43] [nvarchar](50) NULL,
      [Text44] [nvarchar](50) NULL,
      [Text45] [nvarchar](50) NULL,
      [Text46] [nvarchar](50) NULL,
      [Text47] [nvarchar](50) NULL,
      [Text48] [nvarchar](50) NULL,
      [Text49] [nvarchar](50) NULL,
      [Text50] [nvarchar](50) NULL,
      [Date1] [datetime] NULL,
      [Date2] [datetime] NULL,
      [Date3] [datetime] NULL,
      [Date4] [datetime] NULL,
      [Date5] [datetime] NULL,
      [Date6] [datetime] NULL,
      [Date7] [datetime] NULL,
      [Date8] [datetime] NULL,
      [Date9] [datetime] NULL,
      [Date10] [datetime] NULL,
      [Date11] [datetime] NULL,
      [Date12] [datetime] NULL,
      [Date13] [datetime] NULL,
      [Date14] [datetime] NULL,
      [Date15] [datetime] NULL,
      [Date16] [datetime] NULL,
      [Date17] [datetime] NULL,
      [Date18] [datetime] NULL,
      [Date19] [datetime] NULL,
      [Date20] [datetime] NULL,
      [Date21] [datetime] NULL,
      [Date22] [datetime] NULL,
      [Date23] [datetime] NULL,
      [Date24] [datetime] NULL,
      [Date25] [datetime] NULL,
      [Number1] [float] NULL,
      [Number2] [float] NULL,
      [Number3] [float] NULL,
      [Number4] [float] NULL,
      [Number5] [float] NULL,
      [Number6] [float] NULL,
      [Number7] [float] NULL,
      [Number8] [float] NULL,
      [Number9] [float] NULL,
      [Number10] [float] NULL,
      [Number11] [float] NULL,
      [Number12] [float] NULL,
      [Number13] [float] NULL,
      [Number14] [float] NULL,
      [Number15] [float] NULL,
      [Number16] [float] NULL,
      [Number17] [float] NULL,
      [Number18] [float] NULL,
      [Number19] [float] NULL,
      [Number20] [float] NULL,
      [Number21] [float] NULL,
      [Number22] [float] NULL,
      [Number23] [float] NULL,
      [Number24] [float] NULL,
      [Number25] [float] NULL,
      [Memo1] [text] NULL,
      [Memo2] [text] NULL,
      [Memo3] [text] NULL,
      [Memo4] [text] NULL,
      [Memo5] [text] NULL,
      [Memo6] [text] NULL,
      [Memo7] [text] NULL,
      [Memo8] [text] NULL,
      [Memo9] [text] NULL,
      [Memo10] [text] NULL,
 CONSTRAINT [PK__APRCUSTOM__68487DD7] PRIMARY KEY CLUSTERED
(
      [RecordNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT dbo.apcustom ON
                          INSERT      
                           INTO            dbo.APCUSTOM(RecordNo)
                                                      SELECT DISTINCT RecordNo
                                                       FROM         dbo.APSALES

SET IDENTITY_INSERT dbo.aprcustom ON
                          INSERT      
                           INTO            dbo.APRCUSTOM(RecordNo)
                                                      SELECT DISTINCT RecordNo
                                                       FROM         dbo.APRENTS

SET IDENTITY_INSERT dbo.apcustom ON
                          INSERT      
                           INTO            dbo.APCUSTOM(RecordNo)
                                                      SELECT DISTINCT RecordNo
                                                       FROM         dbo.APSALES

SET IDENTITY_INSERT dbo.HMcustom ON
                          INSERT      
                           INTO            dbo.HMCUSTOM(RecordNo)
                                                      SELECT DISTINCT RecordNo
                                                       FROM         dbo.HMSALES

SET IDENTITY_INSERT dbo.IPcustom ON
                          INSERT      
                           INTO            dbo.IPCUSTOM(RecordNo)
                                                      SELECT DISTINCT RecordNo
                                                       FROM         dbo.IPSALES

SET IDENTITY_INSERT dbo.RTcustom ON
                          INSERT      
                           INTO            dbo.RTCUSTOM(RecordNo)
                                                      SELECT DISTINCT RecordNo
                                                       FROM         dbo.APRENTALS


END

'
Avatar of VeeVan
VeeVan

ASKER

Nevermind. I figured it out. I am limited to 2000 characters, so what I have done, is made separate commands for all my items. Works like a charm.

Thank you.

V
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Interesting: you used my approach, but awarded aneesh all the points.  Be sure to verify that it actually worked as you intended.
Avatar of VeeVan
VeeVan

ASKER

Scott. My bad. I didn't even notice that it was a different poster with that comment. I apologize. In my delirium, I forgot to look at the name.

I will post an additional question with points for you to answer. Again, I am sorry.

V
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Np.  I was in such a hurry I forgot to mention one very important thing: you need a USE [?] before your commands in the script:

EXEC sp_msforeachdb '
IF  ? not in (''master'', ''model'', ''msdb'', ''replication'', ''tempdb'')
begin

USE [?]

...rest of script as before...
'
Avatar of VeeVan
VeeVan

ASKER

Yup. Got it. It works. Took me most of yesterday to get it to work, but it works. Here's the link for you.

https://www.experts-exchange.com/questions/22939674/How-to-loop-through-all-databases-and-avoid-system-DB's-in-SQL.html?cid=239&anchorAnswerId=20217947#a20217947