How do we get the definition of tables via sql

Question

How do we get the definition of tables via sql

Viewing 15 posts - 31 through 44 (of 44 total)

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

Brandie Tarvin SSC Guru Points: 173061 More actions · Answer 1

mw112009 (2/12/2016)
I don't have a need to understand how SQL SERVER works. That is not a part of my job.

Honestly, if knowing how SQL Server works is NOT part of your job, you shouldn't be using SQL Server. Seriously. This is a dangerous tool to be goofing off with. And you certainly shouldn't be scripting out object definitions.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

I am pleased with what I picked from the INTERNET. Works well!

However if you do like to share your code ( or solution ) I will be more than happy to know it.

And thanks for engaging

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Actually, I support your efforts 100%.

Please continue to download and run random code that you downloaded from the internet, without any understanding of it, and certainly without caring what it may do.

My rate keeps going up and up when I have to fix the lunacy you introduce on a daily basis.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Brandie Tarvin (2/12/2016)
mw112009 (2/12/2016)
I don't have a need to understand how SQL SERVER works. That is not a part of my job.
Honestly, if knowing how SQL Server works is NOT part of your job, you shouldn't be using SQL Server. Seriously. This is a dangerous tool to be goofing off with. And you certainly shouldn't be scripting out object definitions.

Pretend we are your supervisor. Can you explain the how and why of the code you are running? If you are asked to make a modification to the code, could you do it?

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

This thread has taken a wild turn recently. Let's all try to remember that at SSC we pride ourselves on being professional and not making personal attacks. That is what separates us from other sites like SO. As such I would ask that unless you have something to contribute to the actual question to please refrain from this senseless parading about right and wrong. Keep it professional and non-personal.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

Reply to Brandie

As a developer there is no need to know every nut and bolt of SQL sever.

With time we acquire more knowledge.

BTW- Thanks for your approach trying to teach me how to get the solution. Unfortunately I don't have time for that right now.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 7

Reply to Post #1760966 from Lynn

The answer is "YES" ( I mean I am able to modify the code )

Why do I need the code ?

I have to copy tables to another db ( in another server ) .

Yes, I could do that by doing a backup and restore.

But the script will be very handy and I can get a novice to run a script easily.

So then the objective is to create one script that will have all the table defs.

Hope I answered your question.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

mw112009 (2/12/2016)
Reply to Post #1760966 from Lynn
The answer is "YES" ( I mean I am able to modify the code )
Why do I need the code ?
I have to copy tables to another db ( in another server ) .
Yes, I could do that by doing a backup and restore.
But the script will be very handy and I can get a novice to run a script easily.
So then the objective is to create one script that will have all the table defs.
Hope I answered your question.

And you can do that, create scripts to recreate tables, etc., using SSMS. Just saying.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

mw112009 (2/12/2016)
Reply to Post #1760966 from Lynn
The answer is "YES" ( I mean I am able to modify the code )
Why do I need the code ?
I have to copy tables to another db ( in another server ) .
Yes, I could do that by doing a backup and restore.
But the script will be very handy and I can get a novice to run a script easily.
So then the objective is to create one script that will have all the table defs.
Hope I answered your question.

This is easy via SSMS (right-click DB / Tasks / Generate Scripts).

I can't understand why the thread is so long.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Phil Parkin (2/12/2016)
mw112009 (2/12/2016)
Reply to Post #1760966 from Lynn
The answer is "YES" ( I mean I am able to modify the code )
Why do I need the code ?
I have to copy tables to another db ( in another server ) .
Yes, I could do that by doing a backup and restore.
But the script will be very handy and I can get a novice to run a script easily.
So then the objective is to create one script that will have all the table defs.
Hope I answered your question.
This is easy via SSMS (right-click DB / Tasks / Generate Scripts).
I can't understand why the thread is so long.

+1000. And, if you setup the scripting correctly, you get all the constraints (including FKs), indexes, triggers, schema names, privs, etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mw_sql_developer SSCoach Points: 19445 More actions · Answer 11

Jeff M

What we need is to be able to do via T-SQL and also we need to get the def for all tables ( Not just picking one at a time )

We found the code at another website.

So the issue is resolved.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 12

mw112009 (2/12/2016)
Sean:
Here you go:
Whether it is accurate or not.. Your decision
http://sqlindia.com/generate-table-definitions-using-tsql-sql-server/

if this is the code you intend to use http://sqlindia.com/generate-table-definitions-using-tsql-sql-server/ then please see below

CREATE TABLE [dbo].[TableTest](

[inta] [int] NOT NULL,

[intb] [int] NOT NULL,

[calc] AS ([inta]+[intb]) PERSISTED

)

GO

--OUTPUT from sp_helptable

CREATE TABLE [dbo].[TableTest](

[inta] [int] NOT NULL,

[intb] [int] NOT NULL,

[calc] [int] NULL

)

GO

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 13

mw112009 (2/12/2016)
Jeff M
What we need is to be able to do via T-SQL and also we need to get the def for all tables ( Not just picking one at a time )
We found the code at another website.
So the issue is resolved.

Cool. Please post the link for the website that you found the code on. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 14

Jeff Moden (2/13/2016)
mw112009 (2/12/2016)
Jeff M
What we need is to be able to do via T-SQL and also we need to get the def for all tables ( Not just picking one at a time )
We found the code at another website.
So the issue is resolved.
Cool. Please post the link for the website that you found the code on. Thanks.

I believe that this is the site from reading the posts

http://sqlindia.com/generate-table-definitions-using-tsql-sql-server/

see my post above

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 15

J Livingston SQL (2/13/2016)
Jeff Moden (2/13/2016)
mw112009 (2/12/2016)
Jeff M
What we need is to be able to do via T-SQL and also we need to get the def for all tables ( Not just picking one at a time )
We found the code at another website.
So the issue is resolved.
Cool. Please post the link for the website that you found the code on. Thanks.
I believe that this is the site from reading the posts
http://sqlindia.com/generate-table-definitions-using-tsql-sql-server/
see my post above

Thanks, Graham. I'll check it out. I found what looks like a great article on the subject by Phil Factor. Here's the link.

https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/

If you know me, I'm not adverse to using xp_CmdShell to call PowerShell.

As a bit of a sidebar, his note on using SQLCompare from the command line to generate scripts seems to be what I'd probably use just because its so bloody simple. I'm sure there are other options for that command line. It's in the beginning of the section titled "Automated scripting of objects".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)