How to generate scripts through querys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to generate scripts through querys

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-11-13 : 03:05:11

Hi

How can i generate scripts for a perticular database through query.

please help me.

Thanks

Zakeer Sk

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 03:08:52

you can generate scripts from sql management studio itself

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-11-13 : 03:50:08

i wanna do thru query...can i able to do

Thanks

Zakeer Sk

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-13 : 04:57:08

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/scripts-stored-procedures-and-functions-through-query.aspx

================================================

When you realize you've made a mistake, take immediate steps to correct it.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-13 : 04:57:49

the 2 methods below do not re-create indexes etc.

If you just need to create an empty copy of a table through query use:
SELECT *
INTO dbo.newTable
FROM dbo.oldTable
where 1<>1

Otherwise:
I got this script from
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=4084641&SiteID=17

DECLARE @Table_Definition VARCHAR(MAX),

@TableName VARCHAR(25)

SET @TableName = 'Test'

SET @Table_Definition = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ' +

QUOTENAME(@TableName,'''') + ')' + CHAR(13) + 'BEGIN'

SET @Table_Definition = COALESCE(@Table_Definition + CHAR(13),'') + 'CREATE TABLE('

SELECT @Table_Definition = COALESCE(@Table_Definition,'') +

CHAR(13) + Column_Name + SPACE(1) + UPPER(Data_Type) +

CASE

WHEN data_type IN ('REAL','money','DECIMAL','NUMERIC') THEN

'(' + CONVERT(VARCHAR(3),COALESCE(numeric_precision,'')) + ','+

CONVERT(VARCHAR(3),COALESCE(Numeric_Scale,'')) + ')'

WHEN data_type IN ('CHAR','nvarchar','VARCHAR','NCHAR') THEN

'(' + CAST(isnull(Character_Maximum_Length,'') AS VARCHAR) + ')'

ELSE '' END +

CASE WHEN Is_Nullable='NO' THEN ' NOT NULL,' ELSE ' NULL,' END

FROM Information_Schema.Columns

WHERE Table_Name = @TableName

SET @Table_Definition = STUFF(@Table_Definition,LEN(@Table_Definition),1,CHAR(13) + ')' + CHAR(13) + 'END')

PRINT @Table_Definition

--EXEC (@Table_Definition)

jhkeswani
Starting Member

2 Posts

Posted - 2008-11-13 : 05:23:59

Hi
This query will script the Procedures and Functions

All you have to do is replace DBname by the actual Database Name

exec master..xp_cmdshell
'bcp "Select routine_definition from DBname.information_Schema.routines order by routine_name" queryout "C:\scripts.sql" -c'

After it runs successfully, the file C:\scripts.sql will have the scripts

quote:
Originally posted by shaik.zakeer

Hi

How can i generate scripts for a perticular database through query.

please help me.

Thanks

Zakeer Sk

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-13 : 05:27:19

quote:
Originally posted by lionofdezert

Whole database script can only be created by method mentioned in link, visakh allready sent to you. But if you want to have create script of Views,Sps and Functions you can use sp_helptext'yourobject'.

Don't do the duplicate post

================================================

When you realize you've made a mistake, take immediate steps to correct it.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-13 : 05:42:28

If you want to script the entire database and not just one table then loop through table name using
information_schema.tables as source for tablenames:

DECLARE @Table_Definition VARCHAR(MAX),

@TableName VARCHAR(25)

DECLARE TableCursor CURSOR FOR

select table_catalog + '.' + table_schema + '.' + table_name as TableName
from information_schema.tables
where table_type='base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Table_Definition = 'IF NOT EXISTS(SELECT 1 FROM sys.tables WHERE name = ' +

QUOTENAME(@TableName,'''') + ')' + CHAR(13) + 'BEGIN'

SET @Table_Definition = COALESCE(@Table_Definition + CHAR(13),'') + 'CREATE TABLE('

SELECT @Table_Definition = COALESCE(@Table_Definition,'') +

CHAR(13) + Column_Name + SPACE(1) + UPPER(Data_Type) +

CASE

WHEN data_type IN ('REAL','money','DECIMAL','NUMERIC') THEN

'(' + CONVERT(VARCHAR(3),COALESCE(numeric_precision,'')) + ','+

CONVERT(VARCHAR(3),COALESCE(Numeric_Scale,'')) + ')'

WHEN data_type IN ('CHAR','nvarchar','VARCHAR','NCHAR') THEN

'(' + CAST(isnull(Character_Maximum_Length,'') AS VARCHAR) + ')'

ELSE '' END +

CASE WHEN Is_Nullable='NO' THEN ' NOT NULL,' ELSE ' NULL,' END

FROM Information_Schema.Columns

WHERE Table_Name = @TableName

SET @Table_Definition = STUFF(@Table_Definition,LEN(@Table_Definition),1,CHAR(13) + ')' + CHAR(13) + 'END')

PRINT @Table_Definition

--EXEC (@Table_Definition)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources