SQL Server System Tables: sys.columns
System tables or also known as the system catalog, contain information about all the objects, data types, constraints, configuration options, and resources available to SQL Server. Each database has a system catalog and the structure of the database is defined by this catalog. Knowing the structure is crucial to a database administrator and the database too. The database server uses the catalog to keep order. I know I sometimes overlook this great resource that's available to us. The table we are going to look at in this article is the sys.columns table. This table does return the column names of a particular table but also returns a lot of other information about a column in a table. I will show through example a few ways this table can be used and some of the data that can be extracted.
A good way to begin is to create a small sample table that we can use for our demonstration. First, let's create this table:
--Create sample table CREATE TABLE [dbo].[TestTable1]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](20) NULL, [Address] [nchar](30) NULL, [Email] [nchar](100) NULL ) ON [PRIMARY]
After you have created the table, we need to put some data in it:
Our first example using sys.columns is to simply return all information about our table.
--List Columns in table SELECT * FROM sys.columns WHERE object_id = object_id('TestTable1')
You result should look something like this:
There is alot of good information that is returned in the result set that we will use in the next couple of examples. In our second example we will check to see if a particular column exists in our table:
IF EXISTS(SELECT * FROM sys.columns WHERE Name = 'Name' AND object_id = object_id('TestTable1')) BEGIN select 1 END
Your result should look like this if the column exists:
Nothing is returned if it doesn't:
Our third example I used recently. I needed to Update the data in a table to NULL if that cell was empty. The table had over 70 columns so to say this litte piece of code saved time is an understatement.
--Building code to update multiple columns SELECT 'UPDATE TestTable1 SET ' + name + ' = NULL WHERE ' + name + ' = '''';' FROM sys.columns WHERE object_id = object_id('TestTable1') AND is_nullable = 1;
This is what your result should look like:
It's an Update for each column which you then copy and run these updates. It will give you a result that looks like this:
When you query the table, it should look like this:
Our last example I found to be interesting and is credited to Pinal Dave. The query utilizes a few other tables, types, tables, and schemas, which are in the catalog.
--Get columns with specific datatypes for entire DB - PINAL DAVE SELECT s.name AS 'schema', ts.name AS TableName, c.name AS column_name, c.column_id, SCHEMA_NAME(t.schema_id) AS DatatypeSchema, t.name AS Datatypename ,t.is_user_defined, t.is_assembly_type ,c.is_nullable, c.max_length, c.PRECISION, c.scale FROM sys.columns AS c INNER JOIN sys.types AS t ON c.user_type_id=t.user_type_id INNER JOIN sys.tables ts ON ts.OBJECT_ID = c.OBJECT_ID INNER JOIN sys.schemas s ON s.schema_id = ts.schema_id ORDER BY s.name, ts.name, c.column_id
Our result looks like this(your results will have other tables in it):
This has been our quick look at the SQL Server system catalog and in particular the sys.columns table. Hopefully the examples gave some ideas on how to use them. MS SQL Server isn't the only product that has a system catalog. Other products that are Relational Database Management Systems (RDBMS) such as Oracle, MySQL, and PostgreSQL use them also. For example, MySQL calls their system catalog INFORMATION_SCHEMA tables. If you have anything to add to this leave a comment below.