Menu
![](/uploads/1/2/5/4/125498823/357682675.jpg)
Active1 year, 8 months ago
Local table editing. Editor can edit a DataTable locally, without saving to a database, simply by excluding the ajax option from its configuration. When operating in this mode, Editor can still be used to edit the data, including making use of its field type options and multi-row editing abilities, but the data is not submitted to the server.
Is there a way to generate a create script from an existing table purely in T-SQL (that is without using SMO, since T-SQL does not have access to SMO). Let's say a stored procedure that receives a table name and returns a string that contains the create script for the given table?
Now let me describe the situation I'm facing, as there may be a different way to approach this. I have an instance with several dozen databases. These database all have the same schema, all the same tables, index and so on. They were created as a part of a third party software installation. I need to have a way to work with them so that I can aggregate data from them in ad-hoc manner. Nice people at dba.se have already helped me here How to create a trigger in a different database?
Currently I need to find a way to make a select from a table across all the databases. I have recorded all the database names into a table called
Databasees
and I wrote the following script to execute a select statement on all of them:However the script above fails with the following message:
An explicit value for the identity column in table '#tmp' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Adding this:
does not help, since, I can't specify the column list and keep it generic.
In SQL there is no way to switch the identity on a given table off. You can only drop a column and add a column, which, obviously changes the column order. And if the column order changes, you, again, need to specify the column list, that would be different depending on the table you query.
So I was thinking if I could get the create table scrip in my T-SQL code, I could manipulate it with string manipulation expressions to remove the identity column and also add a column for the Database name to the result set.
Can anyone think of a relatively easy way to achieve what I want?
Aaron Bertrand♦161k1919 gold badges324324 silver badges530530 bronze badges
Andrew SavinykhAndrew Savinykh73333 gold badges1010 silver badges2323 bronze badges
4 Answers
Back in 2007, I asked for an easy way to generate a
CREATE TABLE
script via T-SQL rather than using the UI or SMO. I was summarily rejected.However, SQL Server 2012 makes this very easy. Let's pretend we have a table with the same schema across multiple databases, e.g.
dbo.whatcha
:The following script uses the new
sys.dm_exec_describe_first_results_set
dynamic management function to retrieve the proper data types for each of the columns (and ignoring the IDENTITY
property). It builds the #tmp table you need, inserts from each of the databases in your list, and then selects from #tmp, all within a single dynamic SQL batch and without using a WHILE
loop (that doesn't make it better, just simpler to look at and allows you to ignore Database_Ref_No
entirely :-)).The resulting
PRINT
output:When you are confident it's doing what you expect, just uncomment the
EXEC
.(This trusts you that the schema is the same; it does not validate that one or more of the tables has since been changed, and may fail as a result.)
Aaron Bertrand♦Aaron Bertrand161k1919 gold badges324324 silver badges530530 bronze badges
It is not possible int T-SQL to generate a full create script of a table. At least there is no build in way. you could always write your own 'generator' going through the information
sys.columns
. But in your case you don't need to get the full create script. All you need is the prevent the
SELECT INTO
from copying the identity property. The easiest way to do that is to add a calculation to that column. So instead ofyou need to write
To generate this statement you can again use sys.columns as in this SQL Fiddle
MS SQL Server 2008 Schema Setup:
The two columns we need are
name
and is_identity
:Query 1:Results:
With that we can use a
CASE
statement to generate each column for the column list:Query 2:
Results:
With a little XML trickery we can concatenate all of this together to get the full column list:
Query 3:
Results:
Keep in mind, that you cannot create a #temp table using dynamic SQL and use it outside of that statement as the #temp table goes out of scope once your dynamic sql statement finishes. So you have to either squeeze all your code into the same dynamic SQL string or use a real table. If you need to be able to execute multiple of these scripts/procedures at the same time, you need to us a random table name, otherwise they will step on each other. Something like
QUOTENAME(N'temp_'+CAST(NEWID() AS NVARCHAR(40))
should make a good enough name.Instead of copying all the data around, you could also use a similar technique to just auto generate a view for each table that unions all the incarnations of that table across all databases. Depending on the table size however that might be faster or slower, so you should test it. If you go this route, I would put those views into a separate database.
ypercubeᵀᴹ81.8k1111 gold badges140140 silver badges232232 bronze badges
Sebastian MeineSebastian Meine8,22211 gold badge1919 silver badges2828 bronze badges
There is a good script to achieve this in the SQLServerCentral article:
- Get DDL for any SQL 2005 table (registration required) by Lowell Izaguirre.
The current latest version of the script is also available as text here (stormrage.com).
I wish there was a way to include all the script here, because it works for me. The script is just too long to paste here.
Copyright notice:
Paul White♦59.9k1616 gold badges310310 silver badges489489 bronze badges
Marcello MiorelliMarcello Miorelli6,8812828 gold badges8080 silver badges176176 bronze badges
You can generate a rough
CREATE TABLE
using dynamic SQL from the data in INFORMATION_SCHEMA.COLUMNS
.![Table Editor From Database Script Table Editor From Database Script](/uploads/1/2/5/4/125498823/242962800.png)
If you need to add constraints etc you will need to add information from some of the other
INFORMATION_SCHEMA
views.david25272david25272
Not the answer you're looking for? Browse other questions tagged sql-serversql-server-2012t-sqldynamic-sql or ask your own question.
Many database administrators are comfortable working within the command line. To them, it's as efficient as a work environment can be. To others, however, the command line is too cumbersome to be efficient. Fortunately, for those users, there are plenty of GUIs available to make the task easier. One such GUI is MySQL Workbench. This particular tool is one of the finest locally installed MySQL client tools you'll find. It's cross-platform, open source, and incredibly easy to use.
More about Networking
I'm going to show you just how easy it is to make use of MySQL Workbench. To do so, I'll walk you through the process of creating a database, adding a table to that database, and then adding data to the table.
SEE: Server deployment/migration checklist (Tech Pro Research)
What you need
I'll use MySQL Working 6.3, which has drastically evolved from older versions of the tool. My instance of MySQL Workbench will connect to a MySQL database, housed on Ubuntu Server 18.04. You need to make sure you have MySQL set up for remote connections (See: How to set up MySQL for remote access on Ubuntu Server 16.04 (This also works for Ubuntu 18.04).
I will assume you already have MySQL Workbench installed.
Creating a database
The first thing to do is create a database (aka Schema). Open MySQL Workbench and connect to your database server. From the database home screen (Figure A), right-click a blank spot under the SCHEMAS pane and select Create Schema.
Figure A
In the resulting window (Figure B), give the schema (database) a name and (if necessary) select a Default Collation.
Figure B
Once you name your schema, click the Apply button and, when prompted, review the SQL Script and click Apply again. Once the schema is created, close the SQL Script window. You're ready to create a table.
Creating a table
I've created a database called SERVERS, that will be listed in the SCHEMAS pane. Expand that listing, right-click the Tables entry, and select Create Table. In the new window (Figure C), name the table.
Figure C
Double-click under Column Name, and you should be able to type the name of the first column. Once you do that, you can then select the Datatype for the column (Figure D).
Figure D
Continue adding columns until your table is complete. Click Apply to execute the SQL Script. Your table is now ready for data.
Adding data to a table
It's now time to populate that table with some data. This is where it gets tricky. In previous incarnations of MySQL Workbench, a button could be found on the home screen that took you directly to the table data entry section. That is no more. In order to start adding data to a table, right-click the table (in the SCHEMAS pane) to be modified and click Select Rows. You will then find yourself in a window that allows you to enter data (Figure E).
Figure E
In this window, you can either use the result grid or open the form editor. For the fastest route to success, use the result grid (which is the default). Double-click the first entry in one of your columns and type the data to be added. After entering the necessary data, hit Enter on your keyboard. Double-click under that newly created row and create another. Once you add all of your data, click Apply to execute the SQL Script.
The form editor is just as easy to use; you only need to remember to click the Next Entry button (single right-pointing arrow) to move to the next data row (Figure F), before entering new data.
Figure F
Your database is ready
Congratulations, you successfully created a database, added a table, and entered data into the table. Your database is ready to use.
Open Source Weekly Newsletter
You don't want to miss our tips, tutorials, and commentary on the Linux OS and open source applications. Delivered Tuesdays
Sign up today Sign up today Also see
- How to add data into MySQL tables from the command line (TechRepublic)
- Why does Oracle keep trashing MySQL, its own product? (TechRepublic)
- How to export and import MySQL databases (TechRepublic)
- How to install MySQL on CentOS 7 (TechRepublic)
- MongoDB in 2019: Cloud, transactions, and mobile will be on the agenda (ZDNet)
- 5G mobile networks: A cheat sheet (TechRepublic)
- What is SDN? How software-defined networking changed everything (ZDNet)
- The Best VPN services for 2019 (CNET)
![](/uploads/1/2/5/4/125498823/357682675.jpg)