sqlserver获取所有表和表字段
2024-09-15 08:22:08
SELECT
[number]=a.colorder,
[column] =a.name,
[datatype]=b.name,
[length]=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
[identity]=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
[key]=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,
[isnullable]=case when a.isnullable=1 then '√'else '' end,
[default]=isnull(e.text,''),
[remark]=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 sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
where d.name='BPMS_Organization' order by a.id,a.colorder
SELECT ID = D.ID ,
Field = CASE WHEN A.COLORDER = 1 THEN D.NAME
ELSE ''
END ,
Remark = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '')
ELSE ''
END ,
ParentID = 0 ,
colorder = 0
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 sys.extended_properties F ON D.ID = F.major_id
WHERE a.COLORDER = 1
AND F.minor_id = 0
DECLARE @TableInfo TABLE
(
name VARCHAR(50) ,
[rows] CHAR(11) ,
reserved VARCHAR(50) ,
data VARCHAR(50) ,
index_size VARCHAR(50) ,
unused VARCHAR(50)
)
DECLARE @TableName TABLE ( name VARCHAR(50) )
DECLARE @name VARCHAR(50)
INSERT INTO @TableName
( name
)
SELECT o.name
FROM sysobjects o ,
sysindexes i
WHERE o.id = i.id
AND o.Xtype = 'U'
AND i.indid < 2
ORDER BY i.rows DESC ,
o.name WHILE EXISTS ( SELECT 1
FROM @TableName )
BEGIN
SELECT TOP 1
@name = name
FROM @TableName
DELETE @TableName WHERE name=@name
INSERT INTO @TableInfo
( name ,
[rows] ,
reserved ,
data ,
index_size ,
unused )
EXEC sys.sp_spaceused @name
END
--表名,记录数,使用大小,索引所使使用大小,未用的空间量,表说明
SELECT F.*,p.tdescription FROM @TableInfo F LEFT JOIN (
SELECT name = CASE WHEN A.COLORDER = 1 THEN D.NAME
ELSE ''
END ,
tdescription = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '')
ELSE ''
END
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 sys.extended_properties F ON D.ID = F.major_id
WHERE a.COLORDER = 1
AND F.minor_id = 0 )P ON F.name=p.name
SELECT a.name
FROM SYSCOLUMNS A
INNER JOIN SYSOBJECTS D ON A.ID = D.ID
AND D.XTYPE = 'U'
AND D.NAME <> 'DTPROPERTIES'
WHERE d.name = 'BPMS_Button' AND 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 ) ) )
最新文章
- Mysql5.7.14安装配置
- [TCPIP] IP路由表及选路 Note
- EasyUI中Dialog的使用
- JavaScript开发中的一些问题
- main函数参数的使用
- Redirect HTTP to HTTPS on Tomcat
- gdb调试程序
- div border-radius画圆
- Java——（八）Map之LinkedHashMap、TreeMap、EnumMap实现类
- Java 容器一些知识
- MySQL数据库的安装布局
- 使用脚本删除hive分区中的问题（expecting KW_EXCHANGE near mytable in alter exchange partition）
- Django+Bootstrap+Mysql 搭建个人博客（五）
- 部署代码review和CI
- NOIP2016(D1T2)天天爱跑步题解
- 微信小程序--data的赋值与取值
- VS Code：让你工作效率翻倍的23个插件和23个编辑技巧
- linux下主流的三种远程技术
- 前端-javascript-ECMAScript5.0
- mysqli 更新&插入操作及事务