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:

 tabledata

 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:

 ex1

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

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:

 ex2a

Nothing is returned if it doesn't:

 ex2b

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:

 ex3a

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:

 ex3c

When you query the table, it should look like this:

ex3b

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

 ex4

 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.

 

Author: Christian Apostolu
Christian Apostolu's picture
About:

Owner/founder of Crush The Soul, software developer, amateur athlete, frustrated musician. 

"Once we accept our limits, we go beyond them." - Albert Einstein

 

Category: