Liste de toutes les colonnes SQL avec max la longueur ET la plus grande longueur

Je suis en train d'essayer d'obtenir une liste de toutes les colonnes d'une table avec leurs types de données, les longueurs de données et la longueur de la plus grande valeur dans cette colonne.

J'ai cette SQL pour obtenir les colonnes et les types de données et longueurs:

SELECT 
    Object_Name(c.object_id),
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
WHERE
    c.object_id = OBJECT_ID('MyTable')

Et j'ai cette SQL pour obtenir la longueur maximale d'une valeur

SELECT Max(Len(MyColumn))
FROM MyTable

Mais je ne peux pas comprendre comment les combiner.

Je suis en utilisant MSSQL 2008.

Ce type de DB utilisez-vous?
Ce SGBD vous utilisez? SQL Server? MySQL? Oracle? DB2? etc..
SQL est un langage standard partagé par plusieurs produits de base de données. Si vous vous posez à propos de SQL Server, veuillez ajouter que, en tant que distincte de la balise.

OriginalL'auteur CeejeeB | 2013-01-23

Merci pour les suggestions. Je suis venu avec la solution suivante. Il me récupère les données dont j'ai besoin, mais serait curieux de voir si elle peut être rendue plus efficace.

declare @results table
(
ID varchar(36),
TableName varchar(250),
ColumnName varchar(250),
DataType varchar(250),
MaxLength varchar(250),
Longest varchar(250),
SQLText varchar(250)
)
INSERT INTO @results(ID,TableName,ColumnName,DataType,MaxLength,Longest,SQLText)
SELECT 
NEWID(),
Object_Name(c.object_id),
c.name,
t.Name,
case 
when t.Name != 'varchar' Then 'NA'
when c.max_length = -1 then 'Max' 
else CAST(c.max_length as varchar)
end,
'NA',
'SELECT Max(Len(' + c.name + ')) FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + Object_Name(c.object_id)
FROM    
sys.columns c
INNER JOIN 
sys.types t ON c.system_type_id = t.system_type_id
WHERE
c.object_id = OBJECT_ID('MyTable')    
DECLARE @id varchar(36)
DECLARE @sql varchar(200)
declare @receiver table(theCount int)
DECLARE length_cursor CURSOR
FOR SELECT ID, SQLText FROM @results WHERE MaxLength != 'NA'
OPEN length_cursor
FETCH NEXT FROM length_cursor
INTO @id, @sql
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (theCount)
exec(@sql)
UPDATE @results
SET Longest = (SELECT theCount FROM @receiver)
WHERE ID = @id
DELETE FROM @receiver
FETCH NEXT FROM length_cursor
INTO @id, @sql
END
CLOSE length_cursor
DEALLOCATE length_cursor
SELECT 
TableName, 
ColumnName, 
DataType, 
MaxLength, 
Longest 
FROM 
@results

cela a bien fonctionné - juste ajouté un peu de bits pour permettre à la colonne des noms avec des espaces et aussi commandé les colonnes dans le même ordre qu'ils apparaissent dans le tableau
De toute façon pour obtenir un max de valeur numérique de l'ensemble de colonnes?
Pour les lecteurs intéressés, ce post les détails du code pour des fonctionnalités similaires qui ne nécessite pas l'utilisation de curseurs.
Grand code fonctionne mieux avec une table temporaire plutôt qu'une variable de table que vous pouvez avoir besoin d'interroger\filtrer une table avec un grand nombre de colonnes.

OriginalL'auteur CeejeeB

C'est quelque chose que j'utilise pour les données de profil qui pourrait être utile. Il suffit de changer "le NOM de VOTRE TABLE" pour le nom de votre table. Il est destiné à vous montrer où des colonnes peut être coupé.

DECLARE @YourTableName sysname;
DECLARE @sql nvarchar(MAX) = ''
SET @YourTableName = YOUR TABLE NAME 
CREATE TABLE #resultsTable (columnName varchar(100), columnLargestValueInData int, columnMaxLength int)
DECLARE @whileIter int = 1
DECLARE @whileTotal int  
SELECT @whileTotal = COUNT(*) FROM sys.columns c
INNER JOIN 
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID(@YourTableName)
-- print 'whileTotal: ' + CONVERT(VARCHAR,@whileTotal) -- used for testing
WHILE @whileIter <= @whileTotal
BEGIN
SELECT  @sql =  N'INSERT INTO #resultsTable (columnName,  columnLargestValueInData, columnMaxLength) SELECT ''' + sc.name + ''' AS columnName, max(len([' + sc.name + '])), ' + CONVERT(varchar,sc.max_length) + ' FROM [' + t.name + ']'  
FROM  sys.tables AS t
INNER JOIN sys.columns AS sc ON t.object_id = sc.object_id
INNER JOIN sys.types AS st ON sc.system_type_id = st.system_type_id
WHERE column_id = @whileIter
AND t.name = @YourTableName
AND st.name IN ('char', 'varchar', 'nchar', 'nvarchar')
PRINT @sql
exec sp_executesql @sql
SET @whileIter += 1
END
SELECT * FROM #resultsTable
TRUNCATE TABLE #resultsTable
DROP TABLE #resultsTable

Pourquoi était-ce downrated? Contrairement à la plupart des "réponses" qu'il fait réellement donner le maximum de données dans toutes les colonnes (par opposition à la limite de colonnes)

OriginalL'auteur user3761595

2
```
   SELECT TOP 1 WITH TIES
Object_Name(c.object_id) ObjectName,
c.name [Column Name],
t.Name [Data type],
c.max_length [Max Length]
FROM    
sys.columns c
INNER JOIN 
sys.types t ON c.system_type_id = t.system_type_id
WHERE
c.object_id = OBJECT_ID('MyTable')
ORDER BY c.max_length DESC
```
Possible pour la Récupération au Niveau de la Colonne de Métadonnées avec les Affichages Catalogue besoin d'ajouter à ADHÉRER à cette condition c.user_type_id = t.user_type_id trop
Le max_length champ au-dessus de la sys.les colonnes de la vue est la longueur maximale de la valeur du champ peut avoir. Ce n'est PAS la longueur maximale d'un champ de données à partir de MyTable. Ce que l'OP voulait était de comparer la longueur du type de données pour le maximum de la longueur réelle des données sur le terrain.

OriginalL'auteur Hamlet Hakobyan

corrigé requête ci-dessus

SELECT 
Object_Name(c.object_id),
c.name 'Column Name',
t.name 'Data type',
c.max_length 'Max Length'
FROM    
sys.columns c
INNER JOIN 
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID('tablename')

OriginalL'auteur user3332278

Ici est une version que j'ai utilisé pendant des années. Il remplace par un trait de soulignement pour les espaces de donner la vraie longueur de données avec des espaces.

set nocount on;
declare @TableName varchar(150) = 'TableName';
declare @Schema varchar(20) = 'TableSchema';
declare @Columns varchar(max);
declare @Unpivot varchar(max);
declare @SQL varchar(max);
select  @Columns = STUFF((
select  ',max(len(replace([' + COLUMN_NAME + '],'' '',''_'')))[' + COLUMN_NAME + '/' 
+ isnull(ltrim(CHARACTER_MAXIMUM_LENGTH),DATA_TYPE) + ']' + CHAR(10) + CHAR(9)
from    INFORMATION_SCHEMA.COLUMNS
where   TABLE_SCHEMA = @Schema
and TABLE_NAME = @TableName
order   by ORDINAL_POSITION
for XML PATH('')),1,1,'')
select  @Unpivot = STUFF((
select  ',[' + COLUMN_NAME + '/' + isnull(ltrim(CHARACTER_MAXIMUM_LENGTH),DATA_TYPE) + ']'
from    INFORMATION_SCHEMA.COLUMNS
where   TABLE_SCHEMA = @Schema
and TABLE_NAME = @TableName
order   by ORDINAL_POSITION
for XML PATH('')),1,1,'')
select  @SQL = 
'select DataSize, ColumnName [ColumnName/Size]
from    (
select ' + @Columns + 'from [' + @Schema + '].[' + @TableName + ']
)x 
unpivot (DataSize for ColumnName in (' + @Unpivot + '))p'
print (@SQL)
exec (@SQL)

OriginalL'auteur Keith Gresham

J'ai étendu la solution de CeejeeB l'ajout de nouveaux champs (Min, Max, NullCount et CountDinstict) et la fixation d'un bug entourant db noms avec [].
En plus, j'ai géré la taille de l'unicode types.

N'hésitez pas à essayer le changement de script SQL, mais attention car le script analyse toutes les tables définies par l'utilisateur de courant DB et cela peut prendre beaucoup à la fin...

DECLARE @results TABLE (
ID VARCHAR(36)
,TableName VARCHAR(250)
,ColumnName VARCHAR(250)
,DataType VARCHAR(250)
,MaxLength INT
,Longest INT
,Min VARCHAR(250)
,Max VARCHAR(250)
,NullCount BIGINT
,CountDistinct BIGINT
,SQLText VARCHAR(MAX)
)
INSERT INTO @results (
ID
,TableName
,ColumnName
,DataType
,MaxLength
,SQLText
)
SELECT NEWID() AS ID
,Object_Name(c.object_id) AS TableName
,c.name AS ColumnName
,t.name AS DataType
,CASE 
WHEN t.name NOT IN (
'char'
,'varchar'
,'nchar'
,'nvarchar'
,'sysname'
,'text'
)
THEN c.max_length
WHEN c.max_length = - 1
THEN c.max_length
ELSE CASE 
WHEN t.name IN (
'nchar'
,'nvarchar'
,'sysname'
)
THEN c.max_length / 2
ELSE c.max_length
END
END AS MaxLength
,'SELECT Max(Len(' + CASE t.name
WHEN 'text'
THEN 'cast('
ELSE ''
END + '[' + c.name + ']' + CASE t.name
WHEN 'text'
THEN ' as varchar(max))'
ELSE ''
END + ')) AS MaxLength,
Min(' + CASE t.name
WHEN 'bit'
THEN 'CONVERT(int,'
WHEN 'text'
THEN 'cast('
ELSE ''
END + '[' + c.name + ']' + CASE t.name
WHEN 'bit'
THEN ')'
WHEN 'text'
THEN ' as varchar(max))'
ELSE ''
END + ') AS Min,
Max(' + CASE t.name
WHEN 'bit'
THEN 'CONVERT(int,'
WHEN 'text'
THEN 'cast('
ELSE ''
END + '[' + c.name + ']' + CASE t.name
WHEN 'bit'
THEN ')'
WHEN 'text'
THEN ' as varchar(max))'
ELSE ''
END + ') AS Max, sum(case when ' + '[' + c.name + ']' + ' is null then 1 else 0 end) AS  NullCount, 
COUNT_BIG(DISTINCT ' + CASE t.name
WHEN 'text'
THEN 'cast('
ELSE ''
END + '[' + c.name + ']' + CASE t.name
WHEN 'text'
THEN ' as varchar(max))'
ELSE ''
END + ') AS CountDistinct 
FROM [' + OBJECT_SCHEMA_NAME(c.object_id) + '].[' + Object_Name(c.object_id) + ']' AS SQLText
FROM sys.columns c
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.type = 'U'
DECLARE @id VARCHAR(36)
DECLARE @SQL VARCHAR(MAX)
DECLARE @receiver TABLE (
Longest INT
,Min VARCHAR(250)
,Max VARCHAR(250)
,NullCount BIGINT
,CountDistinct BIGINT
)
DECLARE length_cursor CURSOR
FOR
SELECT ID
,SQLText
FROM @results
OPEN length_cursor
FETCH NEXT
FROM length_cursor
INTO @id
,@SQL
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @receiver (
Longest
,Min
,Max
,NullCount
,CountDistinct
)
EXEC (@SQL)
UPDATE @results
SET Longest = r.Longest
,Min = r.Min
,Max = r.Max
,NullCount = r.NullCount
,CountDistinct = r.CountDistinct
FROM @receiver r
WHERE ID = @id
DELETE
FROM @receiver
FETCH NEXT
FROM length_cursor
INTO @id
,@SQL
END
CLOSE length_cursor
DEALLOCATE length_cursor
SELECT TableName
,ColumnName
,DataType
,MaxLength
,Longest
,Min
,Max
,NullCount
,CountDistinct
FROM @results

OriginalL'auteur Filippo Bottega

0

ajouter: et t de.user_type_id = 167, sinon, vous obtenez de dup pour les non varchars. Je sais qu'il existe d'autres types, c'était une solution rapide pour une table spécifique

après c.object_id = OBJECT_ID(@YourTableName)

OriginalL'auteur alex

Noter que toutes les requêtes mentionnées ci-dessus le rapport de certaines "étrange" tailles - en particulier, pour n... types (de type nvarchar /nchar). Cette légèrement modifiée de la requête, résout ce problème:

DECLARE @tableName AS NVARCHAR(200) = 'Items'
SELECT 
Object_Name(c.object_id) AS 'Table',
c.name AS 'Column Name',
t.name AS 'Data type',
CASE WHEN t.name LIKE 'n%' THEN c.max_length / 2 ELSE c.max_length END AS 'Max Length'
FROM    
sys.columns c
INNER JOIN 
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID(@tableName)

OriginalL'auteur CameO73

-1

La réponse est assez compliqué. Vous devez utiliser SQL dynamique pour mettre ensemble la requête ou de faire le travail dans Excel. Vous avez besoin de combiner les méta-données dans les tables système (je voudrais utiliser Information_Schema.Les colonnes) avec les données de la table elle-même.

Comment le faire, ce qui est expliqué sur les pages 84-90 de mon livre L'Analyse des données à l'Aide de SQL et Excel. La réponse est trop long pour ce site.

OriginalL'auteur Gordon Linoff

-2

Légèrement modifiée, mais fonctionne un régal.

SELECT 
Object_Name(c.object_id),
c.name 'Column Name',
t.name 'Data type',
c.max_length 'Max Length',
MAX(LEN(C.NAME))
FROM    
sys.columns c
INNER JOIN 
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID('<table name>')
GROUP BY 
Object_Name(c.object_id),
c.name ,
t.name ,
c.max_length

pas de MAX(LEN(C. NOM)) vous donne la longueur des colonnes nom n'est pas le contenu de la colonne

OriginalL'auteur user3909732

Vous devez vous connecter pour publier un commentaire.