System catalog and views provided inbuilt with SQL Server, are really wonderful, very useful and handy to get some inside information immediately. Today I want to show you use of following different system catalog and views.
: used to show you list of all objects in databaseSysColumns
: used to show you list of all columns of all tablesInformation_Schema.Columns
: also going to show you the list of columns for all tables, some information are easy to access other than SysColumns but note that SysColumns is powerful than this oneSys.Partitions
: Generally tables and indexes are in at least one partition in SQL Server 2008 so I use it as a handy tool to get total number of rows in each table.
Now, here I present one very small code snippet which will show you Schema Name, Table Name, Total Maximuz size of row in table and total number of rows exits in table. I have used above introduced system catalog to get these information, You may have different combination to get this kind of information.
isc.TABLE_SCHEMA as SchemaName,
so.name as TableName,
SUM(sc.length) AS RowSizeInBytes,
sp.rows as TotRowsInTable
sysobjects so join
syscolumns sc on so.name = OBJECT_NAME(sc.id) join
INFORMATION_SCHEMA.COLUMNS isc on sc.name=isc.COLUMN_NAME and so.name=isc.TABLE_NAME join
sys.partitions sp on OBJECT_NAME(sp.object_id)=so.name
so.type = ‘U’