Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

47 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  
 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