Is there an easy way to break up a database SQL script file into one file per object?

Question

I have the output of SQL Servers Tasks -> Generate Scripts... for all the tables in my database on one file.

Is there an easy way to break up the single SQL script into one file per table?

Obviously, if I could run the Generate Scripts task with the File per object setting selected, I wouldn't be asking this.

Answer 1

Run Task-> Generate Scripts task. In the final wizard page select "script to file" and select 'File per object'. You must specify an existing folder for the output.

Answer 2

Using PowerShell. Without knowing the format of your SQL file this may or may not work. It should at least give you a starting point. It starts writing to the "table.sql" file but later renames the file as tablename.sql

1.   $lineNumber = 0
2.   $inputFile = "test.sql"
3.   foreach ($line in Get-Content $inputFile) {
4.     if ($line -match "create table") { 
5.        $w = [regex]::Replace($line, "^.+\.\[(\w+)\].+$", '$1')
6.        $outFile = "$w.sql"
7.        $lineNumber = 1
8.     }
9.     if (($line -match "use \[dbaInventory\]") -and ($lineNumber -gt 0)) {
10.        Move-Item -LiteralPath "table.sql" -Destination $outFile
11.     }
12.     $line | Out-file -FilePath table.sql -Append
13.  }
14.  Move-Item -LiteralPath "table.sql" -Destination $outFile

Change line 2 for whatever combined sql file you currently have and line 9 to look for whatever database name is appropriate to your script.

Here is a sample of the "test.sql" file I used for testing.

USE [dbaInventory]
GO

/****** Object:  Table [dbo].[tableOne]    Script Date: 11/22/2010 12:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tableOne](
    [colA] [smallint] NULL,
    [colB] [char](1) NULL,
    [colC] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

USE [dbaInventory]
GO

/****** Object:  Table [dbo].[tableTwo]    Script Date: 11/22/2010 12:28:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tableTwo](
    [col_A] [char](1) NULL,
    [col_B] [decimal](10, 2) NULL,
    [col_C] [smallint] NULL,
 CONSTRAINT [Pk_tableTwo] PRIMARY KEY CLUSTERED 
(
    [col_A] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO