August 6, 2018 at 9:35 am
Is there an equivalent to the Tasks -> generate Scripts - Schema and Data tool in ssms that can be run from TSQL or powershell? The primary reason I am looking to do this is to copy a compete database Without having to make a backup. I have permissions on the production Server that are sufficient to generate the creation scripts for schema and data, but do not have permissions to create backups or use the copy database tool.
August 6, 2018 at 9:43 am
In TSQL, there is a function, OBJECT_DEFINITION(). It doesn't work with all object types however.
There is a SCRIPTER method in SQL Server plugin for Powershell.
August 6, 2018 at 10:01 am
Beating my own drum here, but i put together a procedure that queries the metadata to script tables and objects:
I try to maintain it as best I can, it's been around for a while.
https://www.sqlservercentral.com/Forums/FindPost1913801.aspx
there are a few things it does not script like temporal tables, im momort tables,partitioned tables,and exotic stuff like that, but it certainly does a good job.
the article is a bit dated, but the direct links to the latest code are up to date.
here's how i do exactly what you are asking:
CREATE TABLE #MyObjectHierarchy
(
HID int identity(1,1) not null primary key,
ObjectId int,
TYPE int,OBJECTTYPE AS CASE
WHEN TYPE = 1 THEN 'FUNCTION'
WHEN TYPE = 4 THEN 'VIEW'
WHEN TYPE = 8 THEN 'TABLE'
WHEN TYPE = 16 THEN 'PROCEDURE'
WHEN TYPE =128 THEN 'RULE'
ELSE ''
END,
ONAME varchar(255),
OOWNER varchar(255),
SEQ int
)
--our results table
CREATE TABLE #Results(ResultsID int identity(1,1) not null,ResultsText varchar(max) )
--our list of objects in dependancy order
INSERT #MyObjectHierarchy (TYPE,ONAME,OOWNER,SEQ)
EXEC sp_msdependencies @intrans = 1
Update #MyObjectHierarchy SET ObjectId = object_id(OOWNER + '.' + ONAME)
--synonyns are object type 1 Function?!?!...gotta remove them
DELETE FROM #MyObjectHierarchy WHERE objectid in(
SELECT [object_id] FROM sys.synonyms UNION ALL
SELECT [object_id] FROM master.sys.synonyms)
DECLARE
@schemaname varchar(255),
@objname varchar(255),
@objecttype varchar(20),
@FullObjectName varchar(510)
DECLARE cur1 CURSOR FOR
SELECT OOWNER,ONAME,OBJECTTYPE FROM #MyObjectHierarchy ORDER BY HID
OPEN cur1
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
WHILE @@fetch_status <> -1
BEGIN
SET @FullObjectName = @schemaname + '.' + @objname
IF @objecttype = 'TABLE'
BEGIN
INSERT INTO #Results(ResultsText)
EXEC sp_getddl @FullObjectName
END
ELSE IF @objecttype IN('VIEW','FUNCTION','PROCEDURE')--it's a FUNCTION/PROC/VIEW
BEGIN
--CREATE PROC/FUN/VIEW object needs a GO statement
INSERT INTO #Results(ResultsText)
SELECT 'GO'
INSERT INTO #Results(ResultsText)
EXEC sp_helptext @FullObjectName
END
FETCH NEXT FROM cur1 INTO @schemaname,@objname,@objecttype
END
CLOSE cur1
DEALLOCATE cur1
SELECT ResultsText FROM #Results ORDER BY ResultsID
Lowell
August 6, 2018 at 10:37 am
Steven.Grzybowski - Monday, August 6, 2018 9:35 AMIs there an equivalent to the Tasks -> generate Scripts - Schema and Data tool in ssms that can be run from TSQL or powershell? The primary reason I am looking to do this is to copy a compete database Without having to make a backup. I have permissions on the production Server that are sufficient to generate the creation scripts for schema and data, but do not have permissions to create backups or use the copy database tool.
There's usually a reason to limit the permissions. If you keep trying to find alternatives, you'll only get more options unavailable. Request a proper backup to the DBA in charge.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply