Query out tables and sps

Question

Post reply

Query out tables and sps

parthi-1705

SSCrazy Eights

Points: 9277
More actions
March 24, 2010 at 1:38 am

#96643

Hi
How to script all the tables,Views,index,triggers and Sps in a database without using Generate script wizard. i need script which means that if the script is run then i can take the O/P and run in my local server. I dont want datas thats y i am asking for this.I dont want to use Wizard any other way is there. I am just trying possible way
Thanks
Parthi
Thanks
Parthi

Viewing 9 posts - 1 through 8 (of 8 total)

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 1

Are you looking for something like this?

SELECTOBJ.type_desc, OBJ.name, SM.DEfinition

FROMsys.sql_modules SM

JOINsys.objects OBJ

ON OBJ.[object_id] = SM.[object_id]

---------------------------------------------------------------------------------

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

hi i got it

DECLARE @Name varchar(100)

DECLARE contact_cursor CURSOR FOR

select Name from sys.sysobjects where xtype in('p','tr','v') order by Xtype

OPEN contact_cursor

FETCH NEXT FROM contact_cursor

INTO @Name

WHILE @@FETCH_STATUS = 0

BEGIN

exec SP_Helptext @Name

FETCH NEXT FROM contact_cursor

INTO @Name

END

CLOSE contact_cursor

DEALLOCATE contact_cursor

GO

If i run the above in my database it will script all the sps,views,tr.

For tables we cant use this so how to get the design structure of the table including index i need to script

Thanks

Parthi

Thanks
Parthi

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 3

You can use the following to avoid cursor

DECLARE @string VARCHAR(MAX)

SET @string = ''

SELECT @string = @string + 'EXEC ( ''sp_helptext ' + name + ' '')' from sys.sysobjects where xtype in('p','tr','v') order by Xtype

EXEC ( @string )

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

Hi

But What about scripting Tables i need to script tables how to do it.

Thanks

Parthi

Thanks
Parthi

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 5

parthi-1705 (3/24/2010)
Hi
But What about scripting Tables i need to script tables how to do it.
Thanks
Parthi

As per my knowledge by T-SQL Table script can not be generated :unsure:

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

As per my knowledge by T-SQL Table script can not be generated :unsure:

But how cum it is done tro Generate SQL Script in object explorer

Thanks
Parthi

Lowell SSC Guru Points: 323518 More actions · Answer 7

oh you can generate the CREATE TABLE script via TSQL; it's just like so many things in SQL Server, the answers are spread out everywhere, and you have to put it together yourself.

take a look at my stored procedure here:

Get DDL For Any Table

the usage is simple:

exec sp_getDDL tablename

or

exec sp_getDDL 'schemaname.tablename'

I've also contributed an article on it as well:

Get DDL for Any SQL 2005 Table]Get DDL for Any SQL 2005 Table

also, look at my article here:

Exporting your Entire Schema via T-SQL which does exactly what you are asking to do: scripts out every table/function/proc/view in foreign key hierarchy order

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 8

Superb thats what i need thanks a lot u have saved my time

Cheers

Parthi

Thanks
Parthi