Here's my take on it the column type generation, which includes computed columns, UDTs and the numeric & decimal types:

[font="Courier New"]

SELECTQUOTENAME(c.name) + ' '

+CASE WHEN c.is_computed = 0 THEN

CASE...

This is just a table I created to test:

CREATE TABLE [dbo].[Table_1](

[col1] [nchar](10) NOT NULL,

[col2] [numeric](25, 12) NULL,

[col3] [xml] NULL,

[col4] [decimal](18, 0) NULL,

[col5] [varchar](max) NULL,

[col6] [varbinary](50) NULL,

[col7] [money] NULL,

[col8] [ntext] NULL,

[col9]...

Not sure if I have done something wrong here, but when generating the script for a table it's not generating the size for DECIMAL & NUMERIC columns?

As soon as I can convince management that we need Enterprise Edition, I'm sure I will 🙂

Thank you all for your feed back, I had resorted to building a dynamic sql string, it looks like that is going to be the best solution for now

Have a read of this: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/30/sql-server-table-variable-vs-local-temporary-table.aspx

Should help

Is there really no one else in this community who has experienced this problem and found a suitable solution?

Hi Andy

Thanks for this info and I can see how it works when running the query

select * from dbo.Contact where lastname = 'green'

However when trying the following, it reverts to...

OK, here's a simple example that demonstrates my issue, despite the fact that this is such a small data set, it is giving similar executioon plans as my real data....

Thanks for sharing your experience, I tried just restarting SQLAgent, and this seems to have had the same effect.

If the stored procedures are the same within each database, you could create just one copy of each procedure in the master database, mark these master stored procedures as system...

Have a look here

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=126116&p=2

DROP TABLE tblRichTest1

-- Create a table

CREATE TABLE tblRichTest1(

 Column1 VARCHAR(100),

 Column2 VARCHAR(100)

)

GO

-- Add a column

ALTER TABLE tblRichTest1

ADD RichTestID INT IDENTITY

GO

-- Allow updates to system tables

EXEC sp_Configure 'allow updates', 1

RECONFIGURE WITH OVERRIDE

GO

-- Now move...

This might be useful...

declare @fullname varchar(150),

@space1 int,

@space2 int,

@first varchar(50),

@middle varchar(50),

@Last varchar(50)

set @fullname = 'first last'

select @space1 = CHARINDEX(' ',@fullname)

select @space2 = CHARINDEX(' ',@fullname,@space1+1)

select @first = left(@fullname,@space1-1)

if @space2 >...