SQL Server Performance Forum – Threads Archive

System tables information

We are converting from one system to another and certain table information might be helpful to this process. Does anyone know if it is possible, from system tables — or some other way — to get: 1- a list of all tables and the number of rows in each one?
2- a list of all the columns in each table and whether that column has any entries or not?
(i.e. is that column being used?) Thank you.
ekl

---

I think you’lll have to write a TSQL query to read data from sysobjects. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

---

SELECT –LEFT(DB_NAME(),30) AS ‘DbName’,
LEFT(OBJECT_NAME(c.id),30) AS ‘TableName’,
LEFT(c.name,30) AS ‘ColumnName’,
CAST(c.colid AS TINYINT) AS ‘Col#’,
UPPER(LEFT(t.name,13)) AS ‘Type’,
CASE WHEN t.name LIKE ‘%char%’ THEN c.length ELSE c.prec END AS ‘Length’,
CAST(c.scale AS TINYINT) AS ‘#Dec ‘
FROM syscolumns c
INNER JOIN systypes t ON c.xusertype = t.xusertype
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.xtype = ‘U’
AND o.name NOT LIKE ‘dt%’
ORDER BY LEFT(OBJECT_NAME(c.id), 30), [Col#] Feel free to modify as per your needs. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>