Table information – column lengths, counts and unique counts
Hi.
The Transact-SQL script below[1] returns the following information:
- ID – Just an identity column used within the script
- SchemaName – The name of the schema
- TableName – Obviously, the name of the table
- ColumnName
- Type – The data type of the column
- Length – The maximum number of bytes used by the field
- MaxLength – The maximum number of bytes currently used (referring to the data)
- ValueCount – The number of records that have a not NULL value
- ValueUniqueCount – The number of unique not NULL values
You can’t get any length or count information from “hierarchyid”, “geometry” or “geography” fields (SQL Server 2008 specific data types), so these are returned as NULL.
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @Type VARCHAR(100)
DECLARE @Length INT
DECLARE @MaxLength INT
DECLARE @ValueCount INT
DECLARE @ValueUniqueCount INT
DECLARE @ID INT
DECLARE @SQL VARCHAR(1000)
CREATE TABLE Temp_TableInfo
( ID INT PRIMARY KEY IDENTITY (1,1),
SchemaName VARCHAR(100),
TableName VARCHAR(100),
ColumnName VARCHAR(100),
Type VARCHAR(100),
Length INT,
MaxLength INT,
ValueCount INT,
ValueUniqueCount INT
)
DECLARE csr CURSOR FAST_FORWARD FOR
SELECT
schema_name(O.schema_id) AS SchemaName,
O.name AS TableName,
C.name AS ColumnName,
T.name AS Type,
C.max_length AS Length
FROM sys.objects O
INNER JOIN sys.columns C
ON O.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.types T
ON C.user_type_id = T.user_type_id
WHERE O.type = 'U'
ORDER BY
schema_name(O.schema_id),
O.name,
C.column_id
OPEN csr
FETCH NEXT FROM csr
INTO @SchemaName, @TableName, @ColumnName, @Type, @Length
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Temp_TableInfo (SchemaName, TableName, ColumnName, Type, Length)
SELECT @SchemaName, @TableName, @ColumnName, @Type, @Length
SET @ID = @@IDENTITY
IF @Type NOT IN ('xml','hierarchyid','geometry','geography','text','ntext')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET MaxLength = ('
SET @SQL = @SQL + 'SELECT MAX(LEN([' + @ColumnName + '])) '
SET @SQL = @SQL + 'FROM ['+ @SchemaName + '].[' + @TableName + ']) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
IF @Type IN ('text','ntext')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET MaxLength = ('
SET @SQL = @SQL + 'SELECT MAX(DATALENGTH([' + @ColumnName + '])) '
SET @SQL = @SQL + 'FROM ['+ @SchemaName + '].[' + @TableName + ']) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
SET @SQL = 'UPDATE Temp_TableInfo SET ValueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM ['+ @SchemaName + '].[' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
IF @Type NOT IN ('text','ntext')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET ValueUniqueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM ('
SET @SQL = @SQL + 'SELECT DISTINCT [' + @ColumnName + '] '
SET @SQL = @SQL + 'FROM ['+ @SchemaName + '].[' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) X) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
FETCH NEXT FROM csr
INTO @SchemaName, @TableName, @ColumnName, @Type, @Length
END
CLOSE csr
DEALLOCATE csr
SELECT * FROM Temp_TableInfo
DROP TABLE Temp_TableInfo
SET NOCOUNT OFF
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @Type VARCHAR(100)
DECLARE @Length INT
DECLARE @MaxLength INT
DECLARE @ValueCount INT
DECLARE @ValueUniqueCount INT
DECLARE @ID INT
DECLARE @SQL VARCHAR(1000)
CREATE TABLE Temp_TableInfo
( ID INT PRIMARY KEY IDENTITY (1,1),
SchemaName VARCHAR(100),
TableName VARCHAR(100),
ColumnName VARCHAR(100),
Type VARCHAR(100),
Length INT,
MaxLength INT,
ValueCount INT,
ValueUniqueCount INT
)
DECLARE csr CURSOR FAST_FORWARD FOR
SELECT
schema_name(O.schema_id) AS SchemaName,
O.name AS TableName,
C.name AS ColumnName,
T.name AS Type,
C.max_length AS Length
FROM sys.objects O
INNER JOIN sys.columns C
ON O.OBJECT_ID = C.OBJECT_ID
INNER JOIN sys.types T
ON C.user_type_id = T.user_type_id
WHERE O.type = 'U'
ORDER BY
schema_name(O.schema_id),
O.name,
C.column_id
OPEN csr
FETCH NEXT FROM csr
INTO @SchemaName, @TableName, @ColumnName, @Type, @Length
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Temp_TableInfo (SchemaName, TableName, ColumnName, Type, Length)
SELECT @SchemaName, @TableName, @ColumnName, @Type, @Length
SET @ID = @@IDENTITY
IF @Type NOT IN ('xml','hierarchyid','geometry','geography','text','ntext')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET MaxLength = ('
SET @SQL = @SQL + 'SELECT MAX(LEN([' + @ColumnName + '])) '
SET @SQL = @SQL + 'FROM ['+ @SchemaName + '].[' + @TableName + ']) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
IF @Type IN ('text','ntext')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET MaxLength = ('
SET @SQL = @SQL + 'SELECT MAX(DATALENGTH([' + @ColumnName + '])) '
SET @SQL = @SQL + 'FROM ['+ @SchemaName + '].[' + @TableName + ']) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
SET @SQL = 'UPDATE Temp_TableInfo SET ValueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM ['+ @SchemaName + '].[' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
IF @Type NOT IN ('text','ntext')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET ValueUniqueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM ('
SET @SQL = @SQL + 'SELECT DISTINCT [' + @ColumnName + '] '
SET @SQL = @SQL + 'FROM ['+ @SchemaName + '].[' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) X) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
FETCH NEXT FROM csr
INTO @SchemaName, @TableName, @ColumnName, @Type, @Length
END
CLOSE csr
DEALLOCATE csr
SELECT * FROM Temp_TableInfo
DROP TABLE Temp_TableInfo
SET NOCOUNT OFF
As SQL Server 2000 doesn’t use Schema Names, I have adapted the above code to work with SQL Server 2000:
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @Type VARCHAR(100)
DECLARE @Length INT
DECLARE @MaxLength INT
DECLARE @ValueCount INT
DECLARE @ValueUniqueCount INT
DECLARE @ID INT
DECLARE @SQL VARCHAR(1000)
CREATE TABLE Temp_TableInfo
( ID INT PRIMARY KEY IDENTITY (1,1),
TableName VARCHAR(100),
ColumnName VARCHAR(100),
Type VARCHAR(100),
Length INT,
MaxLength INT,
ValueCount INT,
ValueUniqueCount INT
)
DECLARE csr CURSOR FAST_FORWARD FOR
SELECT
O.name AS TableName,
C.name AS ColumnName,
T.name AS Type,
C.length AS Length
FROM sysobjects O
INNER JOIN syscolumns C
ON O.ID = C.ID
INNER JOIN systypes T
ON C.xtype = T.xusertype
WHERE O.type = 'U'
ORDER BY
O.name,
C.colorder
OPEN csr
FETCH NEXT FROM csr
INTO @TableName, @ColumnName, @Type, @Length
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Temp_TableInfo (TableName, ColumnName, Type, Length)
SELECT @TableName, @ColumnName, @Type, @Length
SET @ID = @@IDENTITY
IF @Type NOT IN ('image', 'text', 'ntext', 'money')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET MaxLength = ('
SET @SQL = @SQL + 'SELECT MAX(LEN([' + @ColumnName + '])) '
SET @SQL = @SQL + 'FROM [' + @TableName + ']) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
SET @SQL = 'UPDATE Temp_TableInfo SET ValueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM [' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
SET @SQL = 'UPDATE Temp_TableInfo SET ValueUniqueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM ('
SET @SQL = @SQL + 'SELECT DISTINCT [' + @ColumnName + '] '
SET @SQL = @SQL + 'FROM [' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) X) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
FETCH NEXT FROM csr
INTO @TableName, @ColumnName, @Type, @Length
END
CLOSE csr
DEALLOCATE csr
SELECT * FROM Temp_TableInfo
DROP TABLE Temp_TableInfo
SET NOCOUNT OFF
DECLARE @ColumnName VARCHAR(100)
DECLARE @Type VARCHAR(100)
DECLARE @Length INT
DECLARE @MaxLength INT
DECLARE @ValueCount INT
DECLARE @ValueUniqueCount INT
DECLARE @ID INT
DECLARE @SQL VARCHAR(1000)
CREATE TABLE Temp_TableInfo
( ID INT PRIMARY KEY IDENTITY (1,1),
TableName VARCHAR(100),
ColumnName VARCHAR(100),
Type VARCHAR(100),
Length INT,
MaxLength INT,
ValueCount INT,
ValueUniqueCount INT
)
DECLARE csr CURSOR FAST_FORWARD FOR
SELECT
O.name AS TableName,
C.name AS ColumnName,
T.name AS Type,
C.length AS Length
FROM sysobjects O
INNER JOIN syscolumns C
ON O.ID = C.ID
INNER JOIN systypes T
ON C.xtype = T.xusertype
WHERE O.type = 'U'
ORDER BY
O.name,
C.colorder
OPEN csr
FETCH NEXT FROM csr
INTO @TableName, @ColumnName, @Type, @Length
SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO Temp_TableInfo (TableName, ColumnName, Type, Length)
SELECT @TableName, @ColumnName, @Type, @Length
SET @ID = @@IDENTITY
IF @Type NOT IN ('image', 'text', 'ntext', 'money')
BEGIN
SET @SQL = 'UPDATE Temp_TableInfo SET MaxLength = ('
SET @SQL = @SQL + 'SELECT MAX(LEN([' + @ColumnName + '])) '
SET @SQL = @SQL + 'FROM [' + @TableName + ']) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
END
SET @SQL = 'UPDATE Temp_TableInfo SET ValueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM [' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
SET @SQL = 'UPDATE Temp_TableInfo SET ValueUniqueCount = ('
SET @SQL = @SQL + 'SELECT COUNT(*) FROM ('
SET @SQL = @SQL + 'SELECT DISTINCT [' + @ColumnName + '] '
SET @SQL = @SQL + 'FROM [' + @TableName + '] '
SET @SQL = @SQL + 'WHERE [' + @ColumnName + '] IS NOT NULL) X) '
SET @SQL = @SQL + 'WHERE ID = ' + LTRIM(RTRIM(STR(@ID)))
EXEC (@SQL)
FETCH NEXT FROM csr
INTO @TableName, @ColumnName, @Type, @Length
END
CLOSE csr
DEALLOCATE csr
SELECT * FROM Temp_TableInfo
DROP TABLE Temp_TableInfo
SET NOCOUNT OFF
- [1] Please ensure you test any script taken from my website on a test/development machine, before running on a production server. ↩
I have just amended the code above for SQL Server 2005 to use the user_type_id instead of system_type_id. This will give better results with columns not being duplicated with nvarchar AND sysname. I have also included the DATALENGTH function for returning the length of text fields.