SQL Server queries all table names and rows of data

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