A script to generate a script?

Hi,
I'm using 2005's management studio, and I was wondering if there's
anyway to script the "generate scripts - script wizard" so that when I
want to take a snapshot of my database structure I can just run a quick
script instead of having to use the wizard. The fact that it's a
"wizard" leads me to think that there is a "non-wizard" way of doing
it, but I can't find anything in the docs.
Thanks
Chris.Hmm, just a guess , take a look at SMO object library (former SQL DMO)
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>|||You can use SMO...
check this link.. for a start
http://davidhayden.com/blog/dave/ar...02/09/2795.aspx
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"chrisb" <chrisbuckett@.gmail.com> wrote in message
news:1150792870.625977.257100@.h76g2000cwa.googlegroups.com...
> Hi,
> I'm using 2005's management studio, and I was wondering if there's
> anyway to script the "generate scripts - script wizard" so that when I
> want to take a snapshot of my database structure I can just run a quick
> script instead of having to use the wizard. The fact that it's a
> "wizard" leads me to think that there is a "non-wizard" way of doing
> it, but I can't find anything in the docs.
> Thanks
> Chris.
>

A rewrite of the sp_help_revlogin procedure (use at own risk)

Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).

I have altered the sp_help_revlogin (from Microsoft article # 246133 )

PLEASE TEST/FIX before you use this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @.login_name sysname = NULL AS

DECLARE @.name sysname

DECLARE @.logintype char(1)

DECLARE @.logindisabled int

DECLARE @.binpwd varbinary (256)

DECLARE @.txtpwd sysname

DECLARE @.tmpstr varchar (256)

DECLARE @.SID_varbinary varbinary(85)

DECLARE @.SID_string varchar(256)

IF (@.login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @.login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

IF (@.@.fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @.tmpstr = '/* sp_help_revlogin_2005 script '

PRINT @.tmpstr

SET @.tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'

PRINT @.tmpstr

PRINT ''

PRINT 'DECLARE @.pwd sysname'

WHILE (@.@.fetch_status <> -1)

BEGIN

IF (@.@.fetch_status <> -2)

BEGIN

PRINT ''

SET @.tmpstr = '-- Login: ' + @.name

PRINT @.tmpstr

IF (@.logintype = 'G' OR @.logintype = 'U')

BEGIN -- NT authenticated account/group

IF @.logindisabled = 1

BEGIN -- NT login is denied access

SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''

PRINT @.tmpstr

END

ELSE BEGIN -- NT login has access

SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''

PRINT @.tmpstr

END

END

ELSE IF (@.logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @.binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @.SID_varbinary

IF (@.binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT

SET @.tmpstr = 'SET @.pwd = CONVERT (nvarchar(128), ' + @.txtpwd + ')'

PRINT @.tmpstr

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = NULL, @.sid = ' + @.SID_string

END

PRINT @.tmpstr

END

END

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

I've split this from the thread to which it was posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169513&SiteID=1), as it does not help with decrypting passwords. I've added a more suggestive title as well.

Thanks for your contribution.

Laurentiu

|||

Here's another rewrite of the sp_help_revlogin, which uses the new DDL and also outputs the password policy options:

http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx

Thanks
Laurentiu

A Matter of Style

When creating SQL scripts, what methods do you use when working with them?

More specifically:
How do you split the SQL across files? For example, do you put things relating to each table into separate files (table creation, indexes for the table, etc.), or do you group similar things into the same file (all indexes in one file, all table creation in another file, etc.)? Maybe you just have ONE BIG file that contains all the code?

Thanks for your input!Hello,

in our projects we split the intitscripts per object into separate file.

f.e. one for sequence
one for tables
and so on

and a upper script that connect to the database with thw correct user and calls the single files.

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
and a upper script that connect to the database with thw correct user and calls the single files.


Ahh... this sounds like a good solution! How exactly do you "call" the individual files? Could you post an example?

Thanks!|||Hello,

here is a small example of a batch like we use it. Start the main.sql
in sqlplus. The other script will be called from the main.sql

Hope that help ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hope that help ?


Thanks! I'll have a look... I appreciate you taking the time to post.