tipos - SQL Server: referencia a una columna por nombre en la función agregada

listado de funciones en sql server

Question

Nuestra aplicación utiliza una serie de tablas con muchas columnas varchar (1000) en ellas. Durante el desarrollo del sistema, se consideró que era más que suficiente espacio para que los usuarios ingresen texto. Sin embargo, ahora estamos encontrando problemas con el corte de texto, por lo que parece que algunos usuarios están ingresando más texto que puede contener. Sin embargo, creo que hay un uso indebido desenfrenado de estos campos, y quiero ver qué columnas se están utilizando.

De una pregunta diferente que fue respondida aquí en SO, he encontrado la siguiente consulta para extraer los nombres de las columnas varchar en una tabla:

select
syscolumns.name as [Column],
syscolumns.length as [MaxLength]
from
sysobjects, syscolumns
where sysobjects.id = syscolumns.id
and sysobjects.xtype = ''u''
and sysobjects.name = ''TableWithTooManyVarcharColumns''
and syscolumns.xusertype = 167

pero ahora quiero usar los nombres de estas columnas en una nueva consulta y calcular

SELECT
[Column] as [Name],
[MaxLength],
MAX(LEN([Column])) as [MaxUsedLength],
AVG(LEN([Column])) as [AvgUsedLength],
STDEV(LEN([Column])) as [StdDev]
FROM TableWithTooManyVarcharColumns
INNER JOIN **{{ reference to table from query above }}**

Por supuesto, la primera consulta devuelve el nombre de la columna, mientras que la segunda necesita una referencia a la columna, por lo que no estoy seguro de cómo combinarlas correctamente. ¿Algún gurú de SQL Server capaz de ayudar?

Answer 1

En aras de la exhaustividad, voy a agregar lo que resultó ser mi solución. Se basa en la solución de swasheck, por lo que obtiene pleno crédito por su respuesta, y esta solución es un tanto desordenada, pero debería servir de base para cualquier otra persona que se encuentre con este problema:

DECLARE @TableName nvarchar(100) = ''TableToAnalyze'' -- Change to your table name
declare @SQLStat nvarchar(4000)
declare @ColName nvarchar(100)
declare @MaxLength integer
declare @MaxUsedLength integer
declare @AvgUsedLength float
declare @StdDev float
declare @parm1IN nvarchar(100)
declare @parm2IN integer
declare @parm3IN integer
declare @parm4IN float
declare @parm5IN float
declare @parm1O integer
declare @parm2O float
declare @parm3O float
CREATE TABLE #Details (
ColumnName nvarchar(100) NULL,
MaxLength integer null,
MaxUsedLength integer null,
AvgUsedLength float null,
StdDev float null
)
declare c cursor for
select c.name, c.max_length
from sys.columns c
join sys.tables t
on c.object_id = t.object_id
where t.name = @TableName
and c.user_type_id = 167
DECLARE @ParmDefinition1 NVARCHAR(500)
SET @ParmDefinition1 = N''@parm1IN nvarchar(100),
@parm1O int OUTPUT,
@parm2O float OUTPUT,
@parm3O float OUTPUT''
DECLARE @ParmDefinition2 NVARCHAR(500)
SET @ParmDefinition2 = N''@parm1IN nvarchar(100),
@parm2IN int,
@parm3IN int,
@parm4IN float,
@parm5IN float''
open c
fetch next from c into
@ColName, @MaxLength
while @@FETCH_STATUS = 0
begin
set @SQLStat = N''SELECT @parm1O = MAX(LEN(''+ QUOTENAME(@ColName) + '')),
@parm2O = AVG(LEN(''+ QUOTENAME(@ColName) + '')),
@parm3O = STDEV(LEN(''+ QUOTENAME(@ColName) + ''))
FROM '' + QUOTENAME(@TableName)
EXECUTE sp_executesql @SQLStat, @ParmDefinition1,
@parm1IN = @ColName,
@parm1O = @MaxUsedLength OUTPUT,
@parm2O = @AvgUsedLength OUTPUT,
@parm3O = @StdDev OUTPUT
set @SQLStat = ''INSERT INTO #Details ( ColumnName, MaxLength, MaxUsedLength, AvgUsedLength, StdDev)''
+ '' VALUES(@parm1IN, @parm2IN, @parm3IN, @parm4IN, @parm5IN)''
EXECUTE sp_executesql @SQLStat, @ParmDefinition2,
@parm1IN = @ColName,
@parm2IN = @MaxLength,
@parm3IN = @MaxUsedLength,
@parm4IN = @AvgUsedLength,
@parm5IN = @StdDev
fetch next from c
into @ColName, @MaxLength
end
close c
deallocate c
SELECT * FROM #Details
DROP TABLE #Details

Answer 2

O une dos tablas derivadas o usa un CTE. Por cierto, estás usando una terminología obsoleta para tus referencias de esquema

select
DefinedName = sysdef.name,
Columnname = tbl.name,
DefinedLength = sysdef.max_length,
etc ...
from (
select c.name, c.max_length
from sys.columns c
join sys.tables t
on c.object_id = t.object_id
where t.name = ''TableWithTooManyVarcharColumns''
) sysdef
join (
SELECT
[Column] as [Name],
[MaxLength],
MAX(LEN([Column])) as [MaxUsedLength],
AVG(LEN([Column])) as [AvgUsedLength],
STDEV(LEN([Column])) as [StdDev]
FROM TableWithTooManyVarcharColumns
) tbl
on sysdef.name = tbl.name

O

;WITH sysdef AS (
select c.name, c.max_length
from sys.columns c
join sys.tables t
on c.object_id = t.object_id
where t.name = ''TableWithTooManyVarcharColumns''
),
tbl AS (
SELECT
[Column] as [Name],
[MaxLength],
MAX(LEN([Column])) as [MaxUsedLength],
AVG(LEN([Column])) as [AvgUsedLength],
STDEV(LEN([Column])) as [StdDev]
FROM TableWithTooManyVarcharColumns
)
select
DefinedName = sysdef.name,
Columnname = tbl.name,
DefinedLength = sysdef.max_length,
etc ...
from tbl t
join sysdef d
on tbl.name = d.name

EDITAR

declare @sql nvarchar(4000)
declare @colname nvarchar(100)
declare @max_length nvarchar(15)
declare c cursor for
select c.name, c.max_length
from sys.columns c
join sys.tables t
on c.object_id = t.object_id
where t.name = ''TableWithTooManyVarcharColumns''
open c
fetch next from c into
@colname, @max_length
while @@FETCH_STATUS = 0
begin
set @sql = ''SELECT '''''' + QUOTENAME(@colname) + '''''', '' + @max_length + '' [MaxLength],'' +
''MAX(LEN(''+QUOTENAME(@colname)+'')) as [MaxUsedLength],
AVG(LEN(''+QUOTENAME(@colname)+'')) as [AvgUsedLength],
STDEV(LEN(''+QUOTENAME(@colname)+'')) as [StdDev]
FROM TableWithTooManyVarcharColumns''
exec(@sql)
fetch next from c
into @colname, @max_length
end
close c
deallocate c