December 13, 2006 at 12:05 am
Hi Folks,
How I can generate Database script using TSQL query in Query analyzer.
It should return the result in one column and one row.
Regards,
Ajit
December 13, 2006 at 5:13 am
Is there any reason you can't use EM? (which has this functionality) ..and also lets you decide at which level you want your script(s) generated etc... Assuming that it's DDL you want scripted.. or is it a 'data-generator' you want? ..or both?
/Kenneth
December 13, 2006 at 5:43 am
Just for R&D....
I Just need the table /View DDL scripts and also sp text...
December 13, 2006 at 8:42 am
The proc SP_Helptext will give you proc/trigger/view source
Use MyDB EXEC sp_helptext myProc --OR EXEC mydb.dbo.sp_helptext myProc
For tables, the only way that I am aware of to programmatically generate create scrips is through DMO. You could probably generate tables scripts programmatically if you wanted to get clever with the system tables, it's all there.
SELECT O.Name as TableName, C.name as ColumnName, T.name as type, C.length as Length, o2.name as constraintName --...
FROM sysobjects O
INNER JOIN syscolumns C
ON O.id = C.id
INNER JOIN systypes T
ON C.type = T.type
INNER JOIN Sysconstraints SC
ON O.id = SC.id
INNER JOIN sysobjects O2
ON SC.constId = O2.id
--...
WHERE O.type = 'U'
If you want to go digging in the system tables, be careful.
SQL guy and Houston Magician
December 13, 2006 at 3:10 pm
Right-click on database in EM.
Select All Tasks -> Generate SQL Script.
Read attentively and choose items carefully.
_____________
Code for TallyGenerator
December 13, 2006 at 3:51 pm
If you're doing it for research and learning, good for you. The T-SQL Help from query analyzer will give you all you need to know. Spend some time understanding the dependencies between objects and different syntax.
Also it is useful to script declared referential integrity (and indexes, etc.) separately (as alter table statments as opposed to embedded in the create table statement.)
Seriously, it is a great way to learn, and I think anybody new to SQL server should do it. I did it many times in the past writing tools like EM before they ever existed.
December 13, 2006 at 4:11 pm
SELECT sysobjects.name AS ObjectName, syscomments.text AS DDL
FROM syscomments INNER JOIN
sysobjects ON syscomments.id = sysobjects.id
WHERE (sysobjects.xtype IN ('U', 'V'))
Filter accordingly.
December 14, 2006 at 4:59 am
First of all thanks to all folks responded to my query...
I have managed to get the output to a some extent...I would like to share the same with you guys....
/* DDL script for View / Procedure */
SELECT sysobjects.name AS ObjectName, syscomments.text AS DDL
FROM sysobjects left outer JOIN syscomments
ON syscomments.id = sysobjects.id
WHERE (sysobjects.xtype IN ('V','P'))
and (syscomments.text like 'Create Proc%' or syscomments.text like 'Create View%')
/* Table Structure */
SELECT O.id,O.Name as TableName, C.name as ColumnName,T.name as type,C.length as Length
FROM
sysobjects O Left Outer JOIN
syscolumns C ON O.id = C.id left outer Join
systypes T ON C.xtype = T.xtype
WHere O.xtype = 'U'
/* Primary Key constraints */
select o1.name TableName,o2.name [ConstraintName],COLUMN_NAME from Sysconstraints sc
inner join
sysobjects o1 on o1.id = sc.id
inner join
sysobjects o2 on sc.constid = o2.id
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE on o2.name = Constraint_name
where o2.xtype = 'PK'
/* Foreign Key constraints */
select o1.name TableName,o2.name FKeyName,fkSrc.name SourceTable,srcCol.name SrcColName,fkRef.name RefTable,RefCol.name RefColName
from
Sysconstraints sc inner join
sysobjects o1 on sc.id = o1.id
inner join sysobjects o2 on sc.constid = o2.id
inner join sysforeignkeys fk on sc.constid = fk.constid
inner join sysobjects fkSrc on fk.fkeyid = fksrc.id
inner join syscolumns srcCol on fkSrc.id = srcCol.id and fk.fkey = srcCol.colid
inner join sysobjects fkref on fk.rkeyid = fkref.id
inner join syscolumns refCol on fkref.id = refCol.id and fk.rkey = refCol.colid
where sc.id = 2133582639 and o2.xtype = 'F'
/* Get Default Constraints */
select o.name TableName,c.name ColumnName,cm.text DefalutValue from sysobjects o inner join syscolumns c on o.id = c.id
inner join sysconstraints cons on o.id = cons.id
inner join syscomments cm on cons.constid = cm.id
where c.cdefault <> 0
and o.xtype = 'U'
Keep posting............
Regards,
Ajit
December 14, 2006 at 7:02 am
If you want to get the DDL for objects in query Analyzer right click on the object then select script objet to new window as create you will get the DDL for that object.
December 14, 2006 at 8:59 am
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=747
A very nice and helpful script I found here. Posted by aceandy.
December 14, 2006 at 9:19 am
very cool!
If you're interested in digging deeper into the system tables, you may find this link useful:
http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx
SQL guy and Houston Magician
January 24, 2007 at 12:49 pm
Here's a free tool I wrote to generate scripts against any 2000 or 2005 database. Very useful for getting all your objects under source control.
http://www.elsasoft.org/tools.htm
---------------------------------------
elsasoft.org
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply