SQL Server Tables and Columns

Stored Procedure to list Tables and Columns in your SQL Server Database

If you want a list of the User Tables and Columns in your SQL Server Database you can use this Stored Procedure.

CREATE OR ALTER PROCEDURE [dbo].[uspTableColumn]
AS
WITH
tbl AS (
		SELECT objects.object_id AS Table_ID
		 , objects.name		AS Table_Name
		 
		FROM sys.objects AS objects
		WHERE objects.type IN('U')
	)
SELECT tbl.Table_ID
		, tbl.Table_Name
		, col.column_id		AS Ordinal_Position
		, col.name		AS Column_Name
		, col.is_nullable	AS Allow_Null
		, col.system_type_id	AS ColumnType_ID
		, col.max_length	AS Column_Length
		, col.precision		AS Column_Precision
		, col.scale		AS Column_Scale
		, col.is_identity	AS Column_Identity
FROM sys.columns AS col
	JOIN tbl
		ON col.object_id = tbl.Table_ID
	ORDER BY Table_Name, Ordinal_Position

Using the WITH keyword for Common Table Expresssions (CTEs) we begin by getting a list of tables from the system view ‘ sys.objects’ and aliasing the object ID with Table ID and object name with Table Name.

The type ‘U’ filters out system tables to give you only user tables.

We then get the column deatils from sys.columns where we find a few interesting items.

Firstly, there is no unique database identifier for a column. The column ID is only unique for a table not the database. Its identity within the database is defined by the object ID (Table ID) and column ID. The latter being a count of where the column appears in the table starting at 1 (not zero). If you go into design view for the table in SSMS (SQL Server Management Studio), you can move a column up or down in the order and its column ID will change. However, unlike in an Access db where you can use code to change the ordinal position of a column, you cannot reorder columns in SQL Server using TSQL.

In this SP, we are getting the Column Name directly from sys.columns but in other SPs you can use the function COL_NAME(object id, column id) to get the column name without having to include sys.columns in your SP. For example, you would use this function when building a SP to return index columns.

We’ll look at other SPs to get the PK column in another post but, as I always first create a table (using TSQL) with just the PK before adding other columns, I can be sure that the column id for the PK, in databases that I’ve built, will be 1. Hence COL_NAME(object_id, 1) should always return the PK column name.

That also assumes that you haven’t used compound Primary Keys, because that would be stupid. Although that’s what the designers of SQL Server have done here with object_ID and column_ID in sys.columns. Compound PKs make life hard because you need to join on two columns at once when joining to a child table. We’ll see examples in later posts. The moral of the story – DON’T use compound PKs. Life is hard enough already.

For Varchar and NVarchar the length is self explanatory, however for these types, a length of -1 indicates Max. For other data types, the max length isn’t all that relevant just now. In a later post I’ll cover using TSQL to add columns and define their type and, for decimals, their precision and scale.

Identity indicates an auto incrementing INT which should always be the PK but the PK will not always be an identity column. Occasionally, you will have need for a PK that has values defined at design time. Status IDs may be an example where coding is done for specific values. Very occasionally you may even need to use a PK that is text and we’ll cover that later.

Later we’ll also look at SPs to change table names and column names, and SPs to list Indexes (and index columns), FKs (Foreign Keys), Default values for columns and other constraints.

This all builds up to a framework that allows you to store a list of your tables and their columns in tables in your FE Application and build or modify your database using VBA to run TSQL