July 13, 2017 at 3:29 pm
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)
AS
/*
Source:
https://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html
Example:
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. */
DECLARE
@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
BEGIN
SET @CurrentEnd = CHARINDEX(char(10), @String) -1;
SET @offset = 2;
END
ELSE
BEGIN
SET @CurrentEnd = 4000;
SET @offset = 1;
END; PRINT SUBSTRING(@String, 1, @CurrentEnd);
SET @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822);
END /*End While loop*/
GO
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;
GO
SELECT i = IDENTITY(int,1,1), table_name
INTO #tables
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;
DECLARE
@i tinyint = 1,
@ii tinyint = (SELECT COUNT(*) FROM #tables),
@ddl varchar(max) = '',
@tbl varchar(1000);
DECLARE @xx varchar(max) = '';
-- the loop
WHILE @i <= @ii
BEGIN
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
INSERT @x
EXEC dbo.sp_GetDDLa @tbl;
SET @xx +=
STUFF(CAST((SELECT char(10)+x FROM @x FOR XML PATH(''), TYPE)
as varchar(max)),1,1,'')+'GO'+char(10);
SET @i += 1;
DELETE FROM @x;
END;
-- (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
GO
-- cleanup
DROP TABLE #tables;
GO
-- Itzik Ben-Gan 2001
September 21, 2017 at 11:05 pm
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'.
September 25, 2017 at 6:34 am
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.
December 20, 2017 at 3:56 pm
This was removed by the editor as SPAM
December 20, 2017 at 4:39 pm
Lowell - Monday, April 10, 2017 10:51 AMFor those of you subscribed to this thread, I've made some fixes today for a couple of things i found.
These changes are noted in section V3.17.
When scripting foreign keys, the column name(s) were not quotenamed, so in the rare instance where you had column names that had spaces, dashes, or started with a number, the script was invalid.
Secondly, I'm now 2016 and especially using ColumnStore Indexes pretty regularly, and the old script would create an invalid syntax for the column store index.I plan on adding the scripting of memory optimized tables, but it's not there yet.
So.....
Updated yet again...same old links but new code.
sp_GetDDLa_Latest.txt (Returns Table)
sp_GetDDL_Latest.txt (Returns varchar(max) )
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.
December 20, 2017 at 5:55 pm
Lowell - Monday, April 10, 2017 10:51 AMquotey thing just submitted. sorry, see below.
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
December 20, 2017 at 6:04 pm
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.
Lowell
December 21, 2017 at 5:55 pm
Hi Lowell,
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
February 20, 2019 at 10:01 pm
Lowell - Monday, October 19, 2009 12:48 PMok I was playing with this today and found that i somehow broke the code for an computed columns;I fixed and tested the solution, and here we go again:here is the updated version: sp_GetDDL2005_V306 fixes that, and also tweaks some alignment issues for when a column is defined as a decimal.the join for sys.computed_columns was completely missing, as well as the .definition...i know it worked in the cursor version.Thanks for making me look at this again, guys.
Hey can you please update the link, I am not able to download your updated file. Seems like the site you uploaded is down,
February 21, 2019 at 9:41 am
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
February 22, 2019 at 4:55 pm
Lowell - Thursday, February 21, 2019 9:41 AMall 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) )
February 22, 2019 at 4:56 pm
jhzhao88 - Friday, February 22, 2019 4:55 PMLowell - Thursday, February 21, 2019 9:41 AMall 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) )
Could you please include the system versioned / temporal table script? That will be great! Thanks a lot.
Xiao
April 15, 2024 at 3:52 pm
So, I just found and fixed a bug in the proc. which has been working great so far btw. I found that when the table being generated is in one schema, but references a table in another schema using a FK that the FK generates wrong and uses the schema from the table being generated.
Example:
- Table being generated: [template].[foo] references [dbo].[bar].
- When the FK generates, it will look like this:
CONSTRAINT [FK_Foo_Bar] FOREIGN KEY ([Id]) REFERENCES [template].[Bar] (Id) )
Which is invalid and will throw an exception. I have attached a version of the file that fixes this. Hopefully the OP updates this, and this helps others who run into the same issue.
April 15, 2024 at 3:53 pm
So, I just found and fixed a bug in the proc. which has been working great so far btw. I found that when the table being generated is in one schema, but references a table in another schema using a FK that the FK generates wrong and uses the schema from the table being generated.
Example:
- Table being generated: [template].[foo] references [dbo].[bar].
- When the FK generates, it will look like this:
CONSTRAINT [FK_Foo_Bar] FOREIGN KEY ([Id]) REFERENCES [template].[Bar] (Id) )
Which is invalid and will throw an exception. I have attached a version of the file that fixes this. Hopefully the OP updates this, and this helps others who run into the same issue.
May 6, 2024 at 11:12 pm
Just wanted to ask whether your change is included in the newest version or not.
Viewing 15 posts - 106 through 120 (of 127 total)
You must be logged in to reply to this topic. Login to reply