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;
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."
-- Itzik Ben-Gan 2001