SQL: 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

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
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

1 Comment

Add a Comment
  1. 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.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.