select d.name,a.name ,b.name ,a.length, a.isnullable from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.xtype='U'select rtrim(b.name) as colname ,case when h.id is not null then 'PK' else '' end as primarykey ,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type ,b.length ,case b.isnullable when 0 then 'N' else 'Y' end as [isnull] ,isnull(e.text,'') as [default] ,isnull(c.value,'') as descript from sysobjects a,syscolumns b left outer join sysproperties c on b.id = c.id and b.colid = c.smallid left outer join syscomments e on b.cdefault = e.id left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid where a.id = b.id and a.id = object_id('titles') --tablename改成你要导出的表的名称 order by b.colid SELECT Table_catalog 库名,table_schema 所有者,table_name 表名 FROM test.INFORMATION_SCHEMA.TABLES where table_type='base table'
1.sp_helpdb or sp_databases 2.sp_helplogins or select * from master..sysxlogins 3.sp_tables or select * from sysobjects where xtype='U' or xtype='S'注:上述所列举的命令假定在当前服务器,如果在其他服务器只要使用完全限定的名称即可。
1、SELECT * FROM master..sysdatabases2、SELECT * FROM master..sysxlogins WHERE (srvid IS NULL)3、SELECT name From sysobjects WHERE (xtype = 'u') AND (status > 0) order by name
select name from master..sysdatabases; select name from sysusers select name from sysobjects where xtype = 'u'
,case when h.id is not null then 'PK' else '' end as primarykey
,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type
,b.length
,case b.isnullable when 0 then 'N' else 'Y' end as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as descript
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and a.id = object_id('titles') --tablename改成你要导出的表的名称
order by b.colid
SELECT Table_catalog 库名,table_schema 所有者,table_name 表名
FROM test.INFORMATION_SCHEMA.TABLES where table_type='base table'
2.sp_helplogins or select * from master..sysxlogins
3.sp_tables or select * from sysobjects where xtype='U' or xtype='S'注:上述所列举的命令假定在当前服务器,如果在其他服务器只要使用完全限定的名称即可。
select name from sysusers
select name from sysobjects where xtype = 'u'
sysaltfiles 主数据库 保存数据库的文件
syscharsets 主数据库 字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库 当前配置选项
sysdatabases 主数据库 服务器中的数据库
syslanguages 主数据库 语言
syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
sysprocesses 主数据库 进程
sysremotelogins主数据库 远程登录帐号syscolumns 每个数据库 列
sysconstrains 每个数据库 限制
sysfilegroups 每个数据库 文件组
sysfiles 每个数据库 文件
sysforeignkeys 每个数据库 外部关键字
sysindexs 每个数据库 索引
sysmenbers 每个数据库 角色成员
sysobjects 每个数据库 所有数据库对象
syspermissions 每个数据库 权限
systypes 每个数据库 用户定义数据类型
sysusers 每个数据库 用户