Mega Code Archive

 

Categories / MSSQL Tutorial / System Settings

 

A script that creates a summary of the tables in a database

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)