SQL Server queries all table names and rows of data

Source: Internet
Author: User
Tags rowcount

Original: SQL Server queries all table names and rows of data

Query all indicate
Selectfromwhere xtype='u'select* from sys.tables


Querying all table names and rows in the database
SELECTA.name as [TABLE NAME], B.rows as [RECORD COUNT] fromsysobjects asaINNER JOINsysindexes asB ona.ID=b.idWHERE(A.type= 'u' )         and(B.indidinch(0,1 ) )ORDER  byA.name, B.rowsDESC


Query all indication and space consumption \ line numberSelectobject_name(ID) tablename,8*Reserved/1024x768reserved,RTrim(8*Dpages)+'KB'used,8*(Reserved-Dpages)/1024x768Unused,8*Dpages/1024x768-Rows/1024x768*Minlen/1024x768free,rows--,* fromsysindexeswhereIndid=1Order byTablename,reserveddesc

SELECTT.table_name as [TABLE NAME] ,        MAX(i.rows) as [RECORD COUNT] fromsysindexes I, information_schema. TABLES TWHERET.table_name= object_name(i.id) andT.table_type= 'BASE TABLE'GROUP  byT.table_schema, T.table_name;---------------------------------------------------------------SELECT  object_name(ID) as [TABLE NAME], rowcnt as [RECORD COUNT] fromsysindexesWHEREIndid< 2         and ObjectProperty(ID,'ismsshipped')= 0ORDER  by object_name(ID)

--Create a temporary tableCREATE   TABLE##RowCount    (      [TABLE NAME] VARCHAR( -) ,      [RECORD COUNT] INT    )  --Executing stored proceduresEXECSp_msforeachtable'INSERT INTO # #RowCount ([TABLE name],[record COUNT]) Select"'?"'TableName, COUNT (*) Datacount from?'     --Query ResultsSELECT  * from##RowCountORDER  by [TABLE NAME]    DROP TABLE##RowCount 

--Count the number of all table records in a database--recently the company's database found that the data of the table has been dropped, some data table is recorded as 0, so want to find out in this database which data table records are 0 to narrow the scope of the analysis, you can use the following SQL Statement: CREATE   TABLE#tmptb (tbname sysname, tbrowsINT, tbreservedVARCHAR(Ten), TbdataVARCHAR(Ten), TbindexsizeVARCHAR(Ten), tbunusedVARCHAR(Ten)    ) INSERT   into#tmptbEXECSp_msforeachtable'EXEC sp_spaceused"'?" " SELECT  * from#tmptb--to list all tablesSELECTtbrows, Tbname from#tmptbWHERETbrows= 0 --List table with record data 0ORDER  byTbnameDROP TABLE#tmptb--which--tbname Table name--tbrows Record Count--tbreserved reserved Space--Tbdata Using Space--tbindexsize Index usage Space--tbunused Unused Space

  --SQL Server iterates through the database for all tables and statistics total:  DECLARE @TableName VARCHAR(255); CREATE TABLE#GetRecordingTempTable ([ID] [INT] IDENTITY(1,1)                  not NULL ,      [TableName] VARCHAR(255) not NULL ,      [Recordingcount] INT    ); DECLARETable_cursorCURSOR   for    SELECT  [name]     fromsysobjectsWHEREXtype= 'U'; OPENTable_cursor; FETCH NEXT  fromTable_cursor into @TableName;  while(@ @FETCH_STATUS = 0 )    BEGIN        EXEC('INSERT into #GetRecordingTempTable ([Tablename],[recordingcount]) SELECT" "+@TableName+" ", COUNT (0) from ['+@TableName+'];'); FETCH NEXT  fromTable_cursor into @TableName; END  CLOSETable_cursor; deallocateTable_cursor; SELECT    [TableName]  as [Table name] ,            [Recordingcount]  as [Total Record Count]   from#GetRecordingTempTableORDER  by  [TableName]; DROP TABLE#GetRecordingTempTable; GO

SQL Server queries all table names and rows of data

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.