SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Programatically add 1 to each column size
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

macfam929
Starting Member

USA
1 Posts

Posted - 10/15/2012 :  10:51:00  Show Profile  Reply with Quote
I have a stored procedure that creates a table, then copies that tables schema into another table. What I want to do is add 1 to the size of each of the columns in the new table. (This is for data paralleling purposes, to make sure that no data was truncated.)
Here is what I have so far:


--Declare the basename of the table (do not include rpt or qa, this will get filled in below based on which table it is creating)
Declare @Table nvarchar(255)
select @Table = 'mmmReport'

--Create the Report Table

----First Drop an existing table with that name.
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'rpt' + @Table)
begin
	exec('DROP TABLE [dbo].[rpt' + @Table + ']')
	PRINT 'Table rpt' + @Table +' dropped'
end
else
begin
	print 'No table exists with the name rpt' + @Table
end

----second entire report Schema for a new table is below. Remove what is not needed and update datatypes and sizes for what is remaining. 
Declare @CreateRPT nvarchar(max)
select @CreateRPT = 
'CREATE TABLE [dbo].rpt' + @Table + '(
	[AdminId] [int] NOT NULL,
	[Grade] [varchar](2) NOT NULL,
	[RepLevel] [varchar](3) NOT NULL,
	[ProductId] [int] NOT NULL,
	[BurstGroup] [int] NOT NULL,
CONSTRAINT [PK_rptCrystalSchema] PRIMARY KEY CLUSTERED 
(
	[AdminId] ASC,
	[Grade] ASC,
	[RepLevel] ASC,
	[ProductId] ASC,
	[BurstGroup] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY]
'
----Third Create the actual table. 
exec (@CreateRPT)

----Fourth Display if the table created or failed. 
if @@ERROR <> 0 
begin
	PRINT 'rpt' + @Table + ' was NOT created due to an Error'
end
else
begin
	PRINT 'rpt' + @Table + ' Created'
end

--Create the QA Table with the same name
----First Drop an existing table with that name.
if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'dbo' and TABLE_NAME = 'qa' + @Table)
begin
	exec('DROP TABLE [dbo].[qa' + @Table + ']')
	PRINT 'Table qa' + @Table +' dropped'
end
else
begin
	print 'No table exists with the name qa' + @Table
end
----Second Copy the existing table with qa prefix instead of rpt
Declare @CreateQA nvarchar(max)
Select @CreateQA = 
	'SELECT * INTO dbo.qa' + @Table + ' FROM dbo.rpt' + @Table
EXEC (@CreateQA)
----Third Display if the table create or failed. 
if @@ERROR <> 0 
begin
	PRINT 'qa' + @Table + ' was NOT created due to an Error'
end
else
begin
	PRINT 'qa' + @Table + ' Created'
end


Both tables create correctly, but they are identical. How can I add 1 to the size of each column in the qa table?
(FYI, create code is truncated, the table actually has well over 100 columns)

Mike Jackson
Starting Member

37 Posts

Posted - 10/17/2012 :  08:30:15  Show Profile  Reply with Quote
do a quick search for a script to create a table from sys.columns. Then just add some code to add 1 to the column types that you want to increase size of.

Mike
Go to Top of Page

Mar
Starting Member

41 Posts

Posted - 10/17/2012 :  08:48:29  Show Profile  Reply with Quote
select *
from sys.tables t
join sys.all_columns c on t.object_id = c.object_id
where t.name = 'tablename'

in this query max_length will give you the column width. You can use this value in a SP.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000