February 28, 2008 at 7:47 am
Hi Jason,
We are doing a lot of this sort of thing. You can get create statements for views, stored procedures, user-defined functions, and possibly even table triggers--not sure about that last one--using SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.OBJ_NAME')).
I've seen an article/script in the newsletter that gets this info by concatenating rows from syscomments, I think it is, but on the version I'm using (2005) the results are the same with OBJECT_DEFINITION, even on objects with large definitions.
It's not difficult to generate a CREATE statement for tables using INFORMATION_SCHEMA.COLUMNS system view, but it gets more complicated if you need constraints and indexes.
I haven't yet found a good way to copy an entire SQL agent job definition, and that's what I'm currently after.
February 29, 2008 at 3:55 am
We use the following code to create scripts for specific tables, views, stored procs, and functions. First we create an object list. Then each object is scripted out and the script sent to a text file. I'm not sure just how far this could be expanded. It does work in both SQL Server 2000 and 2005.
I've "generisized" this a bit by removing the extra conditions we use when building the object list to limit this to only the specific objects in which we are interested.
CREATE procedure [dbo].[ScriptObjects]
(
@SourceSVRvarchar(128), -- Source Server
@SourceDBvarchar(128),
@SourceObjectvarchar(128),
@SourceUIDvarchar(128),
@SourcePWDvarchar(128),
@OutFilePathvarchar(128),
@OutFileNamevarchar(128),
@AddDateTimebit -- Add datetime to file name
)
AS
set nocount on
-- UNC paths are allowed in order to save files on other machines.
-- Access must be granted to the account running MSSQL
-- If drive letters are used (C:\), the file will be saved on the server
-- actually running the stored procedure
declare@ScriptTypeint ,
@FileNamevarchar(128) ,
@TmpFileNamevarchar(128) ,
@buffervarchar(8000) ,
@collectionvarchar(128)
declare@contextvarchar(255) ,
@sqlvarchar(8000) ,
@rcint,
@counterint,
@objectmaxint
declare@objServerint ,
@objTransferint ,
@strResultvarchar(255) ,
@strCommandvarchar(255)
IF @OutFileName IS NOT NULL AND @AddDateTime = 1
set @OutFileName = @OutFileName + convert(varchar(8),getdate(),112) +
LEFT(replace(convert(varchar(8),getdate(),108),':',''), 4) + '.sql'
if (@OutFileName is null)
set @OutFileName = 'cnv_script_' + @SourceDB + '_' + convert(varchar(8),getdate(),112) +
LEFT(replace(convert(varchar(8),getdate(),108),':',''), 4) + '.sql'
select@ScriptType= 1|4|64|256, -- 256 causes an append
@FileName= @OutFilePath + @OutFileName ,
@counter= 1
-- get objects to script and object type
create table ##Objects (ind int identity(1, 1), name varchar(128), collection varchar(128))
SET @sql = 'Use ' + @SourceDB + '
insert ##Objects (name, collection)
SELECT TABLE_NAME, ''tables''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
ORDER BY TABLE_NAME
insert ##Objects (name, collection)
select ROUTINE_NAME, ''userdefinedfunctions''
fromINFORMATION_SCHEMA.ROUTINES
whereROUTINE_TYPE = ''FUNCTION''
order by ROUTINE_NAME
insert ##Objects (name, collection)
select ROUTINE_NAME, ''storedprocedures''
fromINFORMATION_SCHEMA.ROUTINES
whereROUTINE_TYPE = ''PROCEDURE''
order by ROUTINE_NAME
insert ##Objects (name, collection)
select TABLE_NAME, ''views''
fromINFORMATION_SCHEMA.VIEWS
order by TABLE_NAME'
EXEC (@SQL)
-- create empty output file
select@sql = 'echo. > ' + @FileName
exec master..xp_cmdshell @sql, no_output
-- prepare scripting object
exec @rc = sp_OACreate 'SQLDMO.SQLServer', @objServer OUT
if @rc <> 0 or @@error <> 0 goto ErrorHnd
--print 'object created'
exec @rc = sp_OAMethod @objServer , 'Connect', NULL, @SourceSVR , @SourceUID , @SourcePWD
if @rc <> 0 or @@error <> 0 goto ErrorHnd
--print 'connection to ' + @SourceSVR + ' made'
select @sql = 'echo USE ' + @SourceDB + ' >> "' + @FileName + '"'
exec master..xp_cmdshell @sql, no_output
-- Script all the objects
SET @ScriptType= 1|4|64|256 -- 256 causes an append
select @objectmax = isnull(max(ind),0) from ##Objects
while (@counter <= @objectmax)
begin
select @SourceObject = name, @collection = collection from ##Objects where ind = @counter
select @sql = 'echo print ''Create = ' + @SourceObject + ''' >> "' + @FileName + '"'
exec master..xp_cmdshell @sql, no_output
Set @sql = 'databases("' + @SourceDB + '").' + @collection + '("' + @SourceObject + '").script'
exec @rc = sp_OAMethod @objServer, @sql , @buffer OUTPUT, @ScriptType , @FileName
set @counter = @counter + 1
end
-- clear up dmo
exec @rc = sp_OAMethod @objServer, 'Disconnect'
if @rc <> 0 or @@error <> 0 goto ErrorHnd
--print 'closing connection'
exec @rc = sp_OADestroy @objServer
if @rc <> 0 or @@error <> 0 goto ErrorHnd
--print 'releasing object'
-- clear up temp table
drop table ##Objects
return 0
ErrorHnd:
return -1
February 29, 2008 at 6:44 am
I wouldn't do this in TSQL. Most of the posts have DMO examples but you will need to use SMO since you need it for 2K5. Write it in one of the .Net languages and take advantage of the Script method for each object in the server. One of the posts mentioned Bill Wunders' site. I believe the SQL 2K5 DDL Archive portion of the SQL CLue tool is available as a beta on his site.
I've written a console application in VB.Net for our shop that does exactly what you listed. I am not a VB programmer but I managed to muddle through it. if you are interested, PM me and I'll zip up the project and email it to you. It isn't very large but too much to post here.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
March 13, 2008 at 12:57 pm
Thank you ALL for your help.
It has been very useful!
I took a hybrid of some of your scripts, to get Stored PRocs / Views / Functions, a static snapshot of tables/indexes, and for the rest (Jobs, Maintenance Plans, etc.) We're going to write a .NET winforms application using SMO.
It would have been nice to dynamically get the tables/indexes....however this will serve our purpose as the tables/indexes won't change very often.
Thanks again!!
March 14, 2008 at 9:15 am
Just another thought, but it's worth mentioning. You could backup the
source database and restore as the target database and delete all the
data from the target database.
The script below is found on this site and works well from what I tested.
Script link http://www.sqlservercentral.com/scripts/T-SQL/61312/
set nocount on
declare @Order int
Set @Order = 1
IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#a'))
DROP TABLE #a
IF Exists(SELECT * FROM tempdb.dbo.sysobjects WHERE [ID] = OBJECT_ID('tempdb.dbo.#b'))
DROP TABLE #b
create table #a ([id] int, [order] int)
create table #b (fid int, rid int)
-- First, delete from tables which are not referenced by any foreign key
insert into #a
select id, 0
from sysobjects left join sysforeignkeys on id = rkeyid
where objectproperty(id, 'isusertable') = 1
and rkeyid is null
-- Make a clone of key pairs from sysforeignkeys
insert into #b
select distinct fkeyid, rkeyid
from sysforeignkeys
-- While there are leaves tables do:
while exists(select distinct f1.fid
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null)
begin
-- Insert leaves first
insert into #a
select distinct f1.fid, @Order
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
-- Delete just "ordered" tables
delete f1
from #b f1 left join #b f2 on f1.fid = f2.rid
where f2.rid is null
Set @Order = @Order + 1
end
-- Insert if something is rest in #b (e.g. self-join)
insert into #a
select distinct fid, @Order
from #b
-- Insert top level tables
insert into #a
select distinct f1.rkeyid, @Order + 1
from sysforeignkeys f1 left join sysforeignkeys f2 on f1.rkeyid = f2.fkeyid
where f2.rkeyid is null
declare @id int
DECLARE @DelCmd nvarchar(1000)
-- Now when we have all tables in temporary table #a
-- we can create dynamic script and execute it
-- If you need list like this often you can create table-valued function
-- starting code from this line, replacing temp table with function call
declare c cursor
for
select [id], max([order])
from #a
group by [id]
order by max([order]) asc
open c
--begin tran
FETCH NEXT FROM c INTO @id, @Order
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @DelCmd = 'TRUNCATE TABLE ' + quotename(Object_name(@id))
EXEC sp_executesql @DelCmd
PRINT cast(quotename(Object_name(@id)) as char(50)) + cast(@@rowcount as char(7)) + ' row(s) deleted '
END
FETCH NEXT FROM c INTO @id, @Order
END
CLOSE c
DEALLOCATE c
--commit
--rollback
drop table #a
drop table #b
set nocount off
March 14, 2008 at 2:35 pm
This might be over simplified but have you tried using the Transfer SQL Server Objects Task in SSIS? There is an option to transfer data which in your case you would set to false. You would then go through the other available options and choose which objects you want to transfer. Then you can have this package actually automate both scripting and creating the objects in your lab environment by importing the package to SQL and scheduling it in a SQL job.
In this job you could drop and recreate the existing database and then run the package. One sticking point is transfering the database users. You just have to make sure that the logins are created on the destination server.
Hope this helps.
Regards.
March 15, 2008 at 9:21 am
I think there is a "Transfer Logins" task in SSIS as well.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 17, 2008 at 6:36 am
Adam, in the bottom portion of your SQL you should be able to replace the entire cursor block with:
EXEC sys.sp_MSforeachtable @command1='TRUNCATE TABLE ?'
March 17, 2008 at 7:03 am
Adam, in the bottom portion of your SQL you should be able to replace the entire cursor block with:
EXEC sys.sp_MSforeachtable @command1='TRUNCATE TABLE ?'
True, but not if foreign keys exist. The script I grabbed from this site checks for foreign keys and clears the tables, in order.
March 18, 2008 at 3:58 am
I agree that getting a vendor product to do the scripting is likely to be the most comprehensive answer.
However if you want something free, take a look at SQLServerFineBuild. It includes SPs that can script most objects in a database. These can easily be wrapped in a SQL Agent job to run at a set time.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply