Generate sql table scripts through SSMSGenerate sql table scripts through queryScript to create table script using Query
Many MS SQL Server professionals use the GUI environment of SQL Server Management Studio to generate the table script or the object explorer where right clicks on the table and generate the table script.
But today, I am going to share a script which I normally use to generate the table script. I have used this script many times and it is very useful which doing the remote DBA assistance because remote connection normally perform slow in GUI environment.
Create Procedure for Generate Table scripts:
CREATE PROCEDURE [dbo].[uspGetCreateTableScript]
@existingTableName SYSNAME, -- Input parameter which will be taking in the existing table name
@newTableName SYSNAME, -- chaning the table name to this one in the final create table script
@scriptOutput VARCHAR(8000) OUTPUT -- this variable will have the final CREATE TABLE ..script in it which can be used in other procedures
AS
SET NOCOUNT ON
/* DECLARATIONS */
DECLARE @sql VARCHAR(8000) -- Variable will return either with the CREATE TABLE.. script or error message
DECLARE @table VARCHAR(100)
declare @cols TABLE (datatype VARCHAR(50))
IF EXISTS (SELECT * FROM Information_Schema.COLUMNS WHERE Table_Name = @existingTableName)
BEGIN
INSERT INTO @cols VALUES('bit')
INSERT INTO @cols VALUES('binary')
INSERT INTO @cols VALUES('bigint')
INSERT INTO @cols VALUES('int')
INSERT INTO @cols VALUES('float')
INSERT INTO @cols VALUES('datetime')
INSERT INTO @cols VALUES('text')
INSERT INTO @cols VALUES('image')
INSERT INTO @cols VALUES('uniqueidentifier')
INSERT INTO @cols VALUES('smalldatetime')
INSERT INTO @cols VALUES('tinyint')
INSERT INTO @cols VALUES('smallint')
INSERT INTO @cols VALUES('sql_variant')
SET @sql=''
SELECT @sql=@sql+
CASE WHEN charindex('(',@sql,1)<=0 THEN '(' ELSE '' END +Column_Name + ' ' +Data_Type +
CASE WHEN Data_Type IN (SELECT datatype FROM @cols) THEN '' ELSE '(' END+
CASE WHEN data_type IN ('real','money','decimal','numeric') THEN cast(isnull(numeric_precision,'') AS VARCHAR)+','+
CASE WHEN data_type IN ('real','money','decimal','numeric') THEN cast(isnull(Numeric_Scale,'') AS VARCHAR) END
WHEN data_type IN ('char','nvarchar','VARCHAR','nchar') THEN cast(isnull(Character_Maximum_Length,'') AS VARCHAR) ELSE '' END+
CASE WHEN Data_Type IN (SELECT datatype FROM @cols)THEN '' ELSE ')' END+
CASE WHEN Is_Nullable='No' THEN ' Not null,' ELSE ' null,' END
FROM Information_Schema.COLUMNS WHERE Table_Name = @existingTableName
SELECT @table = 'CREATE TABLE ' + @newTableName FROM Information_Schema.COLUMNS WHERE table_Name = @existingTableName
SELECT @sql = @table + SUBSTRING (@sql, 1, LEN(@sql)-1) +' )' -- Adding the ending bracket in to the code and assigning it to the return variable
--SELECT @sql AS DDL
--RETURN @sql
SET @scriptOutput = @sql
END
ELSE
BEGIN
SET @sql = 'The table ' + @existingTableName + ' does not exist.'
SET @scriptOutput = @sql
END
|
Syntax:
DECLARE @outputvar VARCHAR(8000)
EXEC uspGetCreateTableScript '<RequireTableName>','<GenerateScriptTableName' , @outputvar OUTPUT
PRINT @outputvar
Eg:
DECLARE @outputvar VARCHAR(8000)
EXEC uspGetCreateTableScript 'IndexFragmentation','IndexFragmentation_N' , @outputvar OUTPUT
PRINT @outputvar
|
Generate sql table scripts through SSMS:
When you are deploying your application, sometimes you may want to generate SQL script with the insert statement of the specific database table. In this article I will show you two easiest ways to do this with SQL Server and SQL Server tools.
1. Using Database Publishing Wizard (“SqlPubWiz.exe”)
If you install Visual studio 2008 / Visual Studio 2008 Express full version, it will automatically install the Microsoft SQL Server Data Publishing Wizard on the following location.
SQL Server Installation Location]\90\Tools\Publishing\[version]\ SqlPubWiz.exe
SQL Server Installation Location]\90\Tools\Publishing\[version]\ SqlPubWiz.exe
If it is not there you can download it from the following URL
http://go.microsoft.com/fwlink/?LinkId=119368
http://go.microsoft.com/fwlink/?LinkId=119368
SQLPubWiz location
When you run the “SqlPubWiz.exe”, it will open the database publishing wizard
Database Publishing Wizard
Click next and enter the database server connection information accordingly.
Database Publishing Wizard – Database Server Connection
Click next, and it will ask you to select the database, which you want to generate the data from
Database Publishing Wizard – Select Database
Next screen will ask to select the Object type. You can use this database publishing wizard to generate data and schema both. In here I consider only for the data generation part. Now choose “Tables” as an option and click next. (Make sure to uncheck “Script all objects in the selected database”)
Database Publishing Wizard – Choose Object Type
Then, the next ask to select the table which you want to generate the data from. Select whatever the table you want and click next.
Database Publishing Wizard – Choose Tables
Next step ask to specify the output location. You can enter the file name and the location there.
Database Publishing Wizard – Select an Output Location
By clicking Next, It shows to select the publishing options. There are three publishing types, Schema only, Data only, or Schema and data. Here you have to select “Data only”.
Database Publishing Wizard – Select Publishing Option
Click next and it will generate the insert data script for you.
Database Publishing Wizard – Publishing Progress
Database Publishing Wizard – Generated Script
2. Using SQL Server Generate Script wizard
SQL Server Generate Script
When you select “Generate Script” menu item, it will open the Generate SQL Server Scripts Wizard dialog.
SQL Server Generate Script – Wizard Dialog
By clicking next, it asks to select the database which you want to generate the script.
SQL Server Generate Script Wizard – Select Database
By clicking Next, It shows to select the script options dialog box. In here, you change the “Script Data” as “True” and click the next button
SQL Server Generate Script Wizard – Choose Script Option
Next screen will ask to select the Object type. Now choose “Tables” as an option and click next.
SQL Server Generate Script Wizard – Choose Object Type
Next screen ask you to select the tables which you want to generate the data from. Select whatever the tables and click next
SQL Server Generate Script Wizard – Choose Tables
Next step ask to specify the output options. If you select “Script to file” as an option, then you can enter the file name and the location there.
SQL Server Generate Script Wizard – Output Option
Next two steps are very strait forward. It shows the script generation summary, and by clicking next it will generate the script with insert statements for the selected tables.
SQL Server Generate Script Wizard – Script Summary
SQL Server Generate Script Wizard – Script Progress
SQL Server Generate Script Wizard – Generated Script