SQL写法 【100分】

Question

1.select [name] from dbo.sysobjects O where left(name,5) = 'table'
取得我需要的表名称。
2.然后根据1中表名称，到相应的表中检索数据个数Count（*）。我想使用1条 SQL文 处理。请大家帮帮忙。

Answer 1

1.select [name] from dbo.sysobjects O where left(name,5) = 'table' 
取得我需要的表名称。 
2.然后根据1中表名称，到相应的表中检索数据个数Count（*）。 我想使用1条 SQL文 处理。请大家帮帮忙。--获取库中所有表及该表的记录数declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)/*
--sql server 2000 自带库 pubs 的结果
name        num         
----------- ----------- 
titleauthor 25
stores      6
sales       21
roysched    86
discounts   3
jobs        14
pub_info    8
employee    43
authors     23
publishers  8
titles      18
*/

Answer 2

上面是所有表,如果只要其中一个表,加入条件即可.declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a where name = ''authors'''
exec(@sql)/*
name         num         
------------ ----------- 
authors      23
*/

Answer 3

declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where left(name,5) = 'table' and xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)

Answer 4

--除了建表，这样算不算一条语句CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT)
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName/*
TableName                                                                                                                                                                                                                                                       RowCnt      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- 
[dbo].[authors]                                                                                                                                                                                                                                                 23
[dbo].[discounts]                                                                                                                                                                                                                                               3
[dbo].[employee]                                                                                                                                                                                                                                                43
[dbo].[jobs]                                                                                                                                                                                                                                                    14
[dbo].[pub_info]                                                                                                                                                                                                                                                8
[dbo].[publishers]                                                                                                                                                                                                                                              8
[dbo].[roysched]                                                                                                                                                                                                                                                86
[dbo].[sales]                                                                                                                                                                                                                                                   21
[dbo].[SolarData]                                                                                                                                                                                                                                               150
[dbo].[stores]                                                                                                                                                                                                                                                  6
[dbo].[titleauthor]                                                                                                                                                                                                                                             25
[dbo].[titles]                                                                                                                                                                                                                                                  18
[dbo].[zhang]                                                                                                                                                                                                                                                   2（所影响的行数为 13 行）*/

Answer 5

要想在
select name = ''' + name + ''' , count(*) as num from ['+name+'] union all 
中加入条件该怎么加呢？

Answer 6

本帖最后由 roy_88 于 2009-03-09 17:39:29 编辑

  

Answer 7

declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as m_count from ['+name+'] union all ' from sysobjects where xtype='u' and left(name,5) = 'table'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)
/*
name        m_count
----------- -----------
tablec      6
Table_6     1
table_rand1 1
tablea      3
table_rand  1
tableb      8
Table_1     1
Table_2     1
Table_3     1
Table_4     1
Table_5     1
table_rand2 1
table_a     6
table_b     2
*/

Answer 8

SELECT DISTINCT M.NAME AS TABLE_NAME,D.ROWS AS ROW_COUNT
FROM SYS.OBJECTS M,SYS.PARTITIONS D 
WHERE M.OBJECT_ID = D.OBJECT_ID AND M.NAME LIKE 'TABLE%'
--更多条件

Answer 9

SELECT 
  A.[NAME] AS TABLE_NAME
  ,B.ROWS AS ROW_COUNT
FROM 
  SYSOBJECTS A
  ,SYSINDEXES B 
WHERE A.[ID] = B.[ID] AND A.[NAME] LIKE 'TABLE%'

Answer 10

acupofnescafe  要注意有重复数据，加个DISTINCT 好些。

Answer 11

如果系统中表名比较长，或者表比较多，超过定义的varchar(8000),会报错的，还是用临时表的方法比较安全