8> SELECT TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName,
9> DATA_TYPE AS Type
10> INTO #TableSummary
11> FROM INFORMATION_SCHEMA.COLUMNS
12> WHERE TABLE_NAME IN
13> (SELECT TABLE_NAME
14> FROM INFORMATION_SCHEMA.TABLES
15> WHERE (TABLE_TYPE = 'BASE TABLE' AND
16> TABLE_NAME NOT IN ('dtproperties', 'TableSummary', 'AllUserTables')))
17>
18> CREATE TABLE #AllUserTables
19> (TableID int IDENTITY, TableName varchar(128))
20> GO
1>
2> INSERT #AllUserTables (TableName)
3> SELECT TABLE_NAME
4> FROM INFORMATION_SCHEMA.TABLES
5> WHERE (TABLE_TYPE = 'BASE TABLE' AND
6> TABLE_NAME NOT IN ('dtproperties', 'TableSummary', 'AllUserTables'))
7> GO
(17 rows affected)
1>
2> DECLARE @LoopMax int, @LoopVar int
3> DECLARE @TableNameVar varchar(128), @ExecVar varchar(1000)
4>
5> SELECT @LoopMax = MAX(TableID) FROM #AllUserTables
6>
7> SET @LoopVar = 1
8>
9> WHILE @LoopVar <= @LoopMax
10> BEGIN
11> SELECT @TableNameVar = TableName
12> FROM #AllUserTables
13> WHERE TableID = @LoopVar
14> SET @ExecVar = 'DECLARE @CountVar int '
15> SET @ExecVar = @ExecVar + 'SELECT @CountVar = COUNT(*) '
16> SET @ExecVar = @ExecVar + 'FROM ' + @TableNameVar + ' '
17> SET @ExecVar = @ExecVar + 'INSERT #TableSummary '
18> SET @ExecVar = @ExecVar + 'VALUES (''' + @TableNameVar + ''','
19> SET @ExecVar = @ExecVar + '''*Row Count*'','
20> SET @ExecVar = @ExecVar + ' @CountVar)'
21> EXEC (@ExecVar)
22> SET @LoopVar = @LoopVar + 1
23> END
24> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT * FROM #TableSummary
3> ORDER BY TableName, ColumnName
4> GO
TableName ColumnName
Type
-------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
--------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
authors_CS *Row Count*
10
authors_CS address
varchar
authors_CS au_fname
varchar
authors_CS au_id
varchar
authors_CS au_lname
varchar
authors_CS city
varchar
authors_CS contract
bit
authors_CS phone
char
authors_CS state
char
authors_CS zip
char
BillingCopy *Row Count*
0
BillingCopy CreditTotal
int
BillingCopy BillingDate
datetime
BillingCopy BillingDueDate
datetime
BillingCopy BillingNumber
int
BillingCopy BillingTotal
int
BillingCopy PaymentTotal
int
BillingCopy TermsID
int
BillingCopy BankerID
int
JaviersData *Row Count*
0
JaviersData SomeColumn
int
MSreplication_options *Row Count*
3
MSreplication_options install_failures
int
MSreplication_options major_version
int
MSreplication_options minor_version
int
MSreplication_options optname
nvarchar
MSreplication_options revision
int
MSreplication_options value
bit
OldBillings *Row Count*
0
OldBillings CreditTotal
int
OldBillings BillingDate
datetime
OldBillings BillingDueDate
datetime
OldBillings BillingNumber
int
OldBillings BillingTotal
int
OldBillings PaymentTotal
int
OldBillings TermsID
int
OldBillings BankerID
int
department_pivot *Row Count*
11
department_pivot budget
float
department_pivot date_month
datetime
department_pivot dept_name
char
department_pivot emp_cnt
int
department_pivot month
int
department_pivot year
int
pub_info *Row Count*
0
pub_info logo
image
pub_info pr_info
text
pub_info pub_id
char
SalesMw *Row Count*
13
SalesMw CD_ID
int
SalesMw QtySold
int
SalesMw SalesDate
datetime
SalesMw StoreID
int
spt_fallback_db *Row Count*
0
spt_fallback_db dbid
smallint
spt_fallback_db name
varchar
spt_fallback_db status
smallint
spt_fallback_db version
smallint
spt_fallback_db xdttm_ins
datetime
spt_fallback_db xdttm_last_ins_upd
datetime
spt_fallback_db xfallback_dbid
smallint
spt_fallback_db xserver_name
varchar
spt_fallback_dev *Row Count*
0
spt_fallback_dev high
int
spt_fallback_dev low
int
spt_fallback_dev name
varchar
spt_fallback_dev phyname
varchar
spt_fallback_dev status
smallint
spt_fallback_dev xdttm_ins
datetime
spt_fallback_dev xdttm_last_ins_upd
datetime
spt_fallback_dev xfallback_drive
char
spt_fallback_dev xfallback_low
int
spt_fallback_dev xserver_name
varchar
spt_fallback_usg *Row Count*
0
spt_fallback_usg dbid
smallint
spt_fallback_usg lstart
int
spt_fallback_usg segmap
int
spt_fallback_usg sizepg
int
spt_fallback_usg vstart
int
spt_fallback_usg xdttm_ins
datetime
spt_fallback_usg xdttm_last_ins_upd
datetime
spt_fallback_usg xfallback_vstart
int
spt_fallback_usg xserver_name
varchar
spt_monitor *Row Count*
1
spt_monitor connections
int
spt_monitor cpu_busy
int
spt_monitor idle
int
spt_monitor io_busy
int
spt_monitor lastrun
datetime
spt_monitor pack_errors
int
spt_monitor pack_received
int
spt_monitor pack_sent
int
spt_monitor total_errors
int
spt_monitor total_read
int
spt_monitor total_write
int
spt_values *Row Count*
2346
spt_values high
int
spt_values low
int
spt_values name
nvarchar
spt_values number
int
spt_values status
int
spt_values type
nchar
Table1 *Row Count*
4
Table1 a
int
Table1 b
datetime
Table1 c
varchar
Table2 *Row Count*
12
Table2 a
int
Table2 message
varchar
titleauthor *Row Count*
24
titleauthor au_id
varchar
titleauthor au_ord
tinyint
titleauthor royaltyper
int
titleauthor title_id
varchar
BankerBalances *Row Count*
10
BankerBalances SumOfBillings
int
BankerBalances BankerID
int
(117 rows affected)