August 28, 2009 at 12:26 am
Hi
Consider the below query to get table_name,column_name,data_type,and constraint_name, I want to add one more new column to this query, i.e to get no of rows in each table. can Any one help me on this
select
kcu.table_name,
kcu.COLUMN_NAME,
COL.DATA_TYPE,
kcu.CONSTRAINT_NAME
from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ,
INFORMATION_SCHEMA.COLUMNS COL
where
KCU.TABLE_NAME = CCU.TABLE_NAME
AND KCU.COLUMN_NAME = CCU.COLUMN_NAME
and CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
and KCU.TABLE_NAME=COL.TABLE_NAME
AND KCU.COLUMN_NAME=COL.COLUMN_NAME
and tc.constraint_TYPE='PRIMARY KEY'
AND kcu.CONSTRAINT_NAME NOT LIKE'FK%'
August 29, 2009 at 2:29 am
You might wanna try this trick:
SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC
but make sure you call
EXEC DBCC UPDATEUSAGE(0)
first, since it might show not so up to date values from time to time. However, do not run this DBCC command often (up to once a week) since it consumes a lot of resources.
Tal Ben Yosef
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply