Prev: Default Trace Logs - increase size
Next: MSSQL / MS Cluster Issue
From: James Hunter Ross on 15 Aug 2008 20:04
Friends, I am trying to programatically list column "dependents" so they
can be elimianted prior to ALTER COLUMN. I am having great success, except
for statistics, such as '_dta_stat_*'. I know what these are, of course,
and have a nice query for SQL Server 2005/2008, but that query won't work on
SQL Server 2000 because there is no sys.stats or sys.stats_columns.

The best I can ascertain is that we can see the statistics as "indexes" in
SQL Server 2000, but that no column association can be gathered.

My basicl goal is to devise a query such that I provide table/column, and
the query returns dependent things that might interfere with ALTER COLUMN.
(Of course, ALTER COLUMN will gripe, but this is for customer database
upgrades, and we want to proactively identify potential issues.) For fun,
I've included my queries below.

2008/2005, but not 2000
select so.name AS ObjectName, ss.name AS FieldVarIndexConstraint, N'Stats'
AS ObjectType, so.name AS TableName, sc.name AS ColumnName
from sys.stats ss
inner join sysobjects so on ss.object_id = so.id
inner join sys.stats_columns tc on ss.object_id = tc.object_id and
ss.stats_id = tc.stats_id
inner join syscolumns sc on ss.object_id = sc.id and sc.colid = tc.column_id

2008/2005/2000, but syscolumn join eliminate all "statistics" indexes.
select o.name, c.name, i.name, c.colid
from sysindexes i
inner join sysobjects o on i.id = o.id
inner join sysindexkeys k on o.id = k.id and i.indid = k.indid
inner join syscolumns c on k.id = c.id and k.colid = c.colid

Any words, even if they to confirm my doom, will be appreciated. Thanks in
advance for trying to decipher this rather technical post...

James Hunter Ross
Senior Software Developer
O'Neil Software, Inc.


 | 
Pages: 1
Prev: Default Trace Logs - increase size
Next: MSSQL / MS Cluster Issue