ok i see the difference.... I was concentrating on max field size, , so my version would simply find the largest used column size a for the MaxActualLength.each max size...

of course someone already had done this, so I didn't follow the rabbit down the hole to reinvent the wheel;

here's a link:

http://sqljunkies.com/WebLog/amachanic/archive/2005/01/25/6897.aspx

the script does...just look for anything with an DefinedSize greater than 8060:

create table BadExample(

varint int,

var1 varchar(8000),

var2 varchar(8000),

var3 varchar(8000),

var4 varchar(8000))

insert into BadExample(varint,var1,var2,var3,var4) values (1,'sometext','more text than before','etc,etc',null)

you get this on creation and insert:

Warning: The...

I'm going to start a [Bankers Rounding] is the [Best Practice] thread now.

Nice Jeff; your version does a much better job of analysis and presenting a basisi for improvements to someone's schema, i like it.

Thanks Jeff; I'm sure you are right...I should initialize the MaxActualLength with the values from syscolumns, and then update...makes the comparison much better.

You contribute a lot to SSC; thanks!

I also...

ok how about this:

it gives results that look something like this:

there is also an underlying table ##tmp, where you can see which columsn are defined but never used

Sugesh getting a program to call them all is pretty easy...the problem is usually dependancy issues....you know, script 42 has to be run after some other specific scripts, something in...

here you go; a snippet i made a while back:

create table ##tmp (TableName varchar(100),DefinedRowSize int)

exec sp_msforeachtable 'INSERT INTO ##TMP Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where...

Note that I'm assuming you have a table named States which has the abbreviation and the long description of the state:

SELECT FirstName, LastName, City, region, country, ModuleTitle,[FileName]

FROM 

(SELECT MAX(CASE WHEN ppd.propertyName = 'FirstName' THEN up.propertyValue END) AS FirstName, 

MAX(CASE WHEN ppd.propertyName = 'LastName' THEN up.propertyValue END) AS LastName, 

MAX(CASE WHEN ppd.propertyName = 'City'     THEN up.propertyValue END) AS City, 

MAX(CASE WHEN ppd.propertyName = 'region'   THEN St.StateAbbrev END) AS region, 

MAX(CASE WHEN ppd.propertyName = 'Country'  THEN up.propertyValue END) AS country, 

up.UserID, l.clickdate, t.UrlType,m.ModuleTitle, f.FileName,u.Email

FROM        ProfilePropertyDefinition AS ppd 

INNER JOIN       UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID 

INNER JOIN       UrlLog l          ON up.UserID = l.UserID 

INNER JOIN       Users u           ON u.UserID = l.UserID 

RIGHT OUTER JOIN UrlTracking t     ON l.UrlTrackingID = t .UrlTrackingID 

LEFT OUTER JOIN  Modules m         ON t .ModuleId = m.ModuleID 

LEFT OUTER JOIN  Files f           ON REPLACE(t .Url, 'FileID=', '') = f.FileId

LEFT OUTER JOIN  STATES st         ON up.propertyValue = St.StateDescription

GROUP BY 

  up.UserID, 

  u.Email, 

  l.clickdate, 

  t.UrlType, 

  m.ModuleTitle, f.FileName) d

WHERE (FirstName IS NOT NULL)  

AND   (LastName IS NOT NULL) 

AND   (clickdate BETWEEN '2007 - 07 - 21' AND '2007 - 07 - 28') 

AND (UrlType = 'F') 

and [FileName] like '%.zip%

about 6 months ago, i was able to download SQL7 english from the MSDN universal subscription.....don't know if that helps, but it's there for testing....

the actual file was named en_SQL7.exe,...

even better, why not just add a calculated field to the table to calculate this formula, isntead of trying to dynamically use a formula:

ALTER TABLE WHATEVER ADD PROJECTEDREVENUE AS 2...

i guess I could have been more clear...3 gig of disk space whenever I mentioned memory.

I admit my servers and development machine are much slower than any SAN, but for...

the issue is the size of memory disk space the system is trying to grab in...

are you sure they are all wrapped in a transaction?

if you are simply running 4 statements, ie 4 updates,a nd the 3rd crashed, the first two might already be committed...