sql脚本查询数据库表,数据,结构,约束等操作的方法
时间:2024-01-19 17:23:26
1.查询当前数据库所有表
SELECT
O.object_id AS TableId,
TableName=O.name ,
TableDesc= O.type
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
WHERE C.column_id=1
ORDER BY TableName
2.查询当前表所有字段,数据,约束
select
tabName=O.NAME,
columnLine=C.column_id,
columnName=C.name,
typeNum=T.name,
typeLength=C.max_length,
fState=ISNULL(G.value,N''),
isAbleNull=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
defaultData=ISNULL(D.definition,N''),
isIdentity=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
isPrimary=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.[object_id] and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = c.[object_id] AND colid=c.column_id))) then '√' else '' end,
isForeign=case when exists(select * from sysforeignkeys fk where C.[object_id]=FK.fkeyid AND C.column_id=FK.fkey)then '√' else '' end,
TabForeignName=ISNULL(IDX.FKName,N''),
OutNameCol=ISNULL(IDX.ns,N'')
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
left JOIN sys.extended_properties G
ON C.[object_id]=G.major_id and c.column_id=g.minor_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
left join sysforeignkeys fk
on C.[object_id]=FK.fkeyid
and C.column_id=FK.fkey
LEFT JOIN -- 索引及主键信息
(
SELECT
IDX.fkeyid,
IDX.fkey,
FKName=o.name,
ns=ss.name
FROM sysforeignkeys IDX
INNER JOIN sys.objects O
ON IDX.rkeyid=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
left join syscolumns ss
on IDX.rkeyid=ss.id
and IDX.RKEY=SS.COLID
)IDX
ON C.[object_id]=IDX.fkeyid
AND C.column_id=IDX.fkey
WHERE O.name=N'{0}' ------要查询的表名
ORDER BY O.name,C.column_id
3.字段
要加单引号varchar,char,nvarchar,nchar,text,ntext,datetime
不需要加int,numeric,bit 不需要加
带长度:[binary],[char],[decimal],[nchar],[numeric],[nvarchar],[varbinary][varchar]
不用带:[bigint],[bit],[datetime],[float],[image],[int],[xml],[timestamp],[tinyint],
[uniqueidentifier],[money],[ntext],[real],[smalldatetime],[smallint],[smallmoney],
[sql_variant],[text]
标签:sql脚本,数据库


猜你喜欢
Django项目中表的查询的操作
2023-04-23 10:22:23
在MySQL中为何不建议使用utf8
2024-01-27 07:07:58

一键生成各种尺寸Icon的php脚本(实例)
2024-06-05 09:48:41
Python实现批量压缩文件/文件夹zipfile的使用
2021-03-08 07:08:21
利用sql函数生成不重复的订单号的代码
2012-01-05 19:02:55
总结Python变量的相关知识
2021-01-22 19:18:47

python安装scipy的方法步骤
2022-02-27 21:10:45

解决Jupyter notebook更换主题工具栏被隐藏及添加目录生成插件问题
2023-11-08 21:32:55

pytorch中的优化器optimizer.param_groups用法
2023-02-12 08:10:08
PHP入门速成(3)
2023-11-20 18:48:03
Python实现手绘图效果实例分享
2021-03-19 11:28:00

golang微服务框架基础Gin基本路由使用详解
2023-07-23 10:31:19
每个ASP程序员必备的知识
2008-09-21 21:34:00
SQL Server 2000如何设置会话上下文信息?
2010-05-18 18:33:00
python和pywin32实现窗口查找、遍历和点击的示例代码
2023-04-06 19:01:23
在Django的模板中使用认证数据的方法
2022-09-08 00:29:45
为2021年的第一场雪锦上添花:用matplotlib绘制雪花和雪景
2022-02-01 23:19:56

php生成与读取excel文件
2023-11-15 06:17:48
详解webpack编译速度提升之DllPlugin
2024-02-23 20:57:10

pandas中的数据去重处理的实现方法
2022-05-13 23:28:50
