I still love this proc Lowell (I'm using your newest version of sp_GetDDLa). I know I've posted that in this thread before. I thought I'd take a moment to include an example of how I've used it lately (earlier today most recently) for to create a "build script" for migrating tables from here to there...
First - sometimes I do this for a lot of tables, other times I have some tables with 100+ columns and lots of indexes and constraints. The Problem in SSMS is that, when I print the output it can get truncated. Someone named Tim Wiseman wrote a great proc to deal with this called LongPrint. Here's the proc:
CREATE PROCEDURE dbo.LongPrint @string nvarchar(MAX)
exec LongPrint @string = 'This String Exists to test the system.'
This procedure is designed to overcome the limitation in the SQL print command that causes
it to truncate strings longer than 8000 characters (4000 for nvarchar).
It will print the text passed to it in substrings smaller than 4000 characters. If there
are carriage returns (CRs) or new lines (NLs in the text), it will break up the substrings
at the carriage returns and the printed version will exactly reflect the string passed.
If there are insufficient line breaks in the text, it will print it out in blocks of 4000
characters with an extra carriage return at that point.
If it is passed a null value, it will do virtually nothing.
NOTE: This is substantially slower than a simple print, so should only be used when actually needed. */
@CurrentEnd bigint, /* track the length of the next substring */
@offset tinyint /*tracks the amount of offset needed */
set @string = replace(replace(@string, char(13)+char(10), char(10)), char(13), char(10));
WHILE LEN(@String) > 1 BEGIN
IF CHARINDEX(char(10), @string) BETWEEN 1 AND 4000
SET @CurrentEnd = CHARINDEX(char(10), @String) -1;
SET @offset = 2;
SET @CurrentEnd = 4000;
SET @offset = 1;
END; PRINT SUBSTRING(@String, 1, @CurrentEnd);
SET @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822);
END /*End While loop*/
Here's a script I wrote today for grabbing a group of tables and throwing them into a build script.
-- (1) A table to hold your list of tables
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;
SELECT i = IDENTITY(int,1,1), table_name
FROM (VALUES ('table1'),('table2'),('table3')) x(table_name);
-- (2) Routine to populate a variable with some with the DDL for multiple tables
SET NOCOUNT ON;
@i tinyint = 1,
@ii tinyint = (SELECT COUNT(*) FROM #tables),
@ddl varchar(max) = '',
DECLARE @xx varchar(max) = '';
-- the loop
WHILE @i <= @ii
SET @tbl = 'dbo.'+(SELECT table_name FROM #tables WHERE i = @i); -- get the next table in the list
DECLARE @x TABLE (x varchar(8000)); -- I need this for an INSERT EXEC
EXEC dbo.sp_GetDDLa @tbl;
SET @xx +=
STUFF(CAST((SELECT char(10)+x FROM @x FOR XML PATH(''), TYPE)
SET @i += 1;
DELETE FROM @x;
-- (3) Required (remove this to see why I use it)
SET @xx = REPLACE(@xx, ')GO', ')'+char(10)+'GO');
-- (4) Print the results in SSMS
EXEC dbo.LongPrint @xx; -- LongPrint allows me to print my DDL without it getting truncated
DROP TABLE #tables;
-- Itzik Ben-Gan 2001
Using http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt on SQL Server 2008 I get the following error in trying to compile the procedure sp_GetDDL
Msg 102, Level 15, State 1, Procedure sp_GetDDL, Line 882
Incorrect syntax near 'obj'.
Probably the database in wich you try to compile is with compatibility level 80 (sql server 2000).
The cross apply work only from compatibility level 90 (sql server 2005).
Try to compile it in MASTER db.
Lowell - Monday, April 10, 2017 10:51 AM
Hi Lowell, thank you so much for the great work. I have been using it as a function and it works very well.
I do have one issue: The stored procedure/view/function (not table) are created as their very original names. If I change the name from "lighting.pr_Ameren_Store_Comm_report" to 'lighting.pr_report', it does not script with the name "lighting.pr_report", instead it always creates the original name proc. From what I can see, the system does not update to the new name as the definition in sys.sql_modules and the text in sys.syscomments are still using the original names.I wonder if there is a way we can fix it...
The script for this case is "IF OBJECT_ID('[lighting].[pr_report]') IS NOT NULL DROP PROCEDURE [lighting].[pr_report] GO CREATE PROC [lighting].[pr_Ameren_Store_Comm_report] AS ..." .
Lowell - Monday, April 10, 2017 10:51 AM
I was wondering if you can add something for system-versioned temporal table. We have quite a lot of temporal tables. Thank you very much for your great work.
the naming thing is caused by using the sp_rename command; there are a few questions of the day on the same issue.
when you use sp_rename on a view/procedure/function, the original definition remains untouched in sys.sql_modules, while the name in sys.objects is modified.
However, when you script via smo/ssms, it is nice enough to swap the name out for you, but I had not considered it important previously. it would be some sort of find and replace, but I could not know, programmatically, where the
/* lots of comments*/
CREATE PROC / CREATE PROCEDURE exists in the script definition, and considering whitespace between those multiple variations would exist in the object_definition; it's not a simply replace oldvalue/newvalue. I am not actually parsing the procedure into tokens the way you can in a programming language.
I'll spend a little time looking, but it's not an easy fix.
As far as the temporal tables related to the existing table, yes, that is actually pretty easy to do. I'll start looking at this again and will post after testing. I have a number of other unpublished changes I made recently to add tot eh current version.
I am currently using find and replace to handle the rename issue as I don’t always have permissions to drop and recreate in production. It is not a big deal though.
I am glad the temporal table scripting is an easy fix. Currently I just add a script in the end to define the system time and turn on the system versioning if the property says it’s a temporal table. I am looking forward to the new update.
I love the return table DDL version as well.
Thanks a lot for the quick reply and the great work!
- - Xiao
Lowell - Monday, October 19, 2009 12:48 PM
Hey can you please update the link, I am not able to download your updated file. Seems like the site you uploaded is down,
all those interim versions from 5+ years ago are gone,and only the latest are maintained.
these are the links, which is @ version 3.18, i need to post my latest changes and improvements again soon, a lot of improvements are in versions 3.19 and 3.20.
When i do, it's the same links anyway.
sp_GetDDLa_Latest.txt (Returns Table)
sp_GetDDL_Latest.txt (Returns varchar(max) )
Lowell - Thursday, February 21, 2019 9:41 AM
jhzhao88 - Friday, February 22, 2019 4:55 PM
Could you please include the system versioned / temporal table script? That will be great! Thanks a lot.
Viewing 12 posts - 106 through 116 (of 116 total)