用如下语句取出每个表的字段 select [name] from sysdatabase where object_name(id)='表名' order by colorder然后再判断哪些字段没有数据
select [name] from sysdatabase where object_name(id)='表名' order by colorder 利用系统表sysdatabase
SELECT 表名 = case when a.colorder=1 then d.name else '' end, 表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end, 字段序号 = a.colorder, 字段名 = a.name, 标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, 主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end, 类型 = b.name, 占用字节数 = a.length, 长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'), 小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 允许空 = case when a.isnullable=1 then '√'else '' end, 默认值 = isnull(e.text,''), 字段说明 = isnull(g.[value],'') FROM syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid left join sysproperties f on d.id=f.id and f.smallid=0 --where d.name='要查询的表' --如果只查询指定表,加上此条件 order by d.name,a.colorder
我执行,怎么提示 对象名sysdatabase无效
SELECT * FROM INFORMATION_SCHEMA.TABLES --查找所有的表 SELECT * FROM INFORMATION_SCHEMA.COLUMNS --查找所有的字段--判断表中是否有数据可以用下面的语句 sp_msforeachtable 'if exists (select * from ?) print ''?'''
select name from syscolumns where id=object_id(N'tablename')
在master库中 select * from master.dbo.sysdatabases就可以了
SELECT * FROM INFORMATION_SCHEMA.TABLES --查找所有的表 SELECT * FROM INFORMATION_SCHEMA.COLUMNS --查找所有的字段 GO --判断表中是否有数据可以用下面的语句 sp_msforeachtable 'if exists (select * from ?) print ''?'''不要一起执行,分开一个个执行
-- 不知对你的问题有没有效果 --检查当前数据库中所有表的记录数、未使用空间等信息。 if object_id('GET_TABLEINFO') is not null drop procedure GET_TABLEINFO go create procedure GET_TABLEINFO as begin if object_id('TABLESPACEINFO') is not null drop table TABLESPACEINFO create table TABLESPACEINFO ( nameinfo varchar(50), rowsinfo int, reserved varchar(20), datainfo varchar(20), index_size varchar(20), unused varchar(20) )
declare INFO_CURSOR cursor for select o.name from dbo.sysobjects o where objectproperty(o.id,N'istable')=1 and o.name not like N'#%%' order by o.name open INFO_CURSOR fetch next from INFO_CURSOR into @tablename while @@fetch_status=0 begin execute sp_executesql N'insert into TABLESPACEINFO exec sp_spaceused @tbname', N'@tbname varchar(255)', @tbname=@tablename fetch next from INFO_CURSOR into @tablename end close INFO_CURSOR deallocate INFO_CURSOR end goexec GET_TABLEINFO--查询运行该存储过程后得到的结果 select * from TABLESPACEINFO order by cast(left(ltrim(rtrim(reserved)),len(ltrim(rtrim(reserved)))-2) as int) descselect * from TABLESPACEINFO order by rowsinfo desc
-- 显示没有记录的表 sp_msforeachtable 'if not exists (select * from ?) print ''?''' go 显示字段的使用情况create procedure p_test ( @table_name varchar(1000) ) as begin declare @sql varchar(max) set @sql = '' select @sql = @sql + ',count(' + column_name + ') as ' + column_name from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name select @sql = 'select '''+ @table_name + ''' as table_name' + @sql +' from '+ @table_name exec(@sql) end godeclare @sql varchar(max) set @sql = '' select @sql = @sql + 'exec p_test''' + table_name + ''';' from INFORMATION_SCHEMA.TABLES exec (@sql)--哪些等于0的字段就是该字段都为NULL
select [name] from sysdatabase where object_name(id)='表名' order by colorder然后再判断哪些字段没有数据
利用系统表sysdatabase
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sysproperties g
on
a.id=g.id and a.colid=g.smallid
left join
sysproperties f
on
d.id=f.id and f.smallid=0
--where d.name='要查询的表' --如果只查询指定表,加上此条件
order by
d.name,a.colorder
SELECT * FROM INFORMATION_SCHEMA.TABLES --查找所有的表
SELECT * FROM INFORMATION_SCHEMA.COLUMNS --查找所有的字段--判断表中是否有数据可以用下面的语句
sp_msforeachtable 'if exists (select * from ?) print ''?'''
select * from master.dbo.sysdatabases就可以了
SELECT * FROM INFORMATION_SCHEMA.COLUMNS --查找所有的字段
GO
--判断表中是否有数据可以用下面的语句
sp_msforeachtable 'if exists (select * from ?) print ''?'''不要一起执行,分开一个个执行
--检查当前数据库中所有表的记录数、未使用空间等信息。
if object_id('GET_TABLEINFO') is not null
drop procedure GET_TABLEINFO
go
create procedure GET_TABLEINFO
as
begin
if object_id('TABLESPACEINFO') is not null
drop table TABLESPACEINFO
create table TABLESPACEINFO
(
nameinfo varchar(50),
rowsinfo int,
reserved varchar(20),
datainfo varchar(20),
index_size varchar(20),
unused varchar(20)
)
declare @tablename varchar(255),
@cmdsql varchar(500)
declare INFO_CURSOR cursor for
select o.name
from dbo.sysobjects o
where objectproperty(o.id,N'istable')=1
and o.name not like N'#%%'
order by o.name
open INFO_CURSOR
fetch next from INFO_CURSOR into @tablename
while @@fetch_status=0
begin
execute sp_executesql N'insert into TABLESPACEINFO exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname=@tablename
fetch next from INFO_CURSOR
into @tablename
end
close INFO_CURSOR
deallocate INFO_CURSOR
end
goexec GET_TABLEINFO--查询运行该存储过程后得到的结果
select *
from TABLESPACEINFO
order by cast(left(ltrim(rtrim(reserved)),len(ltrim(rtrim(reserved)))-2) as int) descselect *
from TABLESPACEINFO
order by rowsinfo desc
-- 显示没有记录的表
sp_msforeachtable 'if not exists (select * from ?) print ''?'''
go
显示字段的使用情况create procedure p_test
(
@table_name varchar(1000)
)
as
begin
declare @sql varchar(max)
set @sql = ''
select @sql = @sql + ',count(' + column_name + ') as ' + column_name
from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table_name select @sql = 'select '''+ @table_name + ''' as table_name' + @sql +' from '+ @table_name
exec(@sql)
end
godeclare @sql varchar(max)
set @sql = ''
select @sql = @sql + 'exec p_test''' + table_name + ''';' from INFORMATION_SCHEMA.TABLES
exec (@sql)--哪些等于0的字段就是该字段都为NULL
楼上是显示的
table_name, col1, col2, col3
test 3 0 5
你只要做个行列转换,然后根据我对你另一个帖子的回答就能够搞定了, 试一下吧