SQL Server – System populated columns

 

Background

Thankfully in SQL Server there are some column decorators that specify that the system should auto-populate the column for us.

Recently, I needed to have a quick list of which columns those are.

SQL

Here is a working code that I will come back and tighten up:

Code


select 
          [table] = 
                      quotename(tblSS.name)
                    + '.'
                    + quotename(tblSO.name)

        , [column] = tblSC.name
        
        , [dataType] = tblST.name
        
        , [citationPremise]
            = case

                when (tblSC.[is_identity] =1) 
					then 'Identity'

                when (tblSC.[is_computed] =1) 
					then 'Computed'

                when (tblST.[name] = 'timestamp') 
					then 'Timestamp'

              end

from   sys.objects tblSO

inner join sys.schemas tblSS

    on tblSO.schema_id = tblSS.schema_id

inner join sys.columns tblSC

    on tblSO.object_id = tblSC.object_id

inner join sys.types tblST

    on  tblSC.system_type_id = tblST.system_type_id
    and tblSC.user_type_id = tblST.user_type_id

where tblSO.is_ms_shipped = 0

and tblSO.[type] = 'U'

and   (

                ( tblSC.[is_identity] = 1)
            or  ( tblSC.[is_computed] = 1)
            or  ( [tblST].[name]
                    in
                        (
                            'timestamp'		
                        )	
                )
      )

order by
          [table] asc
        , [column] asc

Output

SQLServerAutoPopulated_Bigfix_BESReporting_20180713_1056AM

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s