[SQL] 常用查询脚本
查询哪些存储过程使用了某个表
select b.name from syscomments a,sysobjects b where a.id=b.id and a.text LIKE '%ftblogkpicontentnew%'
查询某个存储过程被哪些语句使用过
declare @object nvarchar(400)
set @object='up_ReportMst_Insert'
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%'+@object+'%'
order by routine_type
select routine_name,routine_definition,routine_type
from information_schema.routines
where routine_definition like '%'+@object+'%'
order by routine_type
select *
from sysobjects o, syscomments s
where o.id = s.id
and text like '%'+@object+'%'
and o.xtype = 'P'
select *
from sysobjects o, syscomments s
where o.id = s.id
and text like '%'+@object+'%'
and o.xtype = 'P'
查询某个数据库表约束
use ArisamCafe
go
SELECT sysobjects.id objectId,OBJECT_NAME(sysobjects.parent_obj) tableName,
sysobjects.name constraintName, sysobjects.xtype AS constraintType,
syscolumns.name AS columnName
FROM sysobjects INNER JOIN sysconstraints
ON sysobjects.xtype in('C', 'F', 'PK', 'UQ', 'D')
AND sysobjects.id = sysconstraints.constid
LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
--WHERE OBJECT_NAME(sysobjects.parent_obj)='tb_PrjServiceSell'
use ArisamKW
go
EXEC sp_helpconstraint 'tb_PrjServiceSell'
根据现有表获取更新脚本
select 'update ArisamDB.dbo.tb_Items set url=N''' +isnull((cast(y.urlNew as nvarchar(MAX))),'')
+''' where companycode=N''' +isnull((cast(y.companycode as nvarchar(MAX))),'')
+''' and listid=N'''+isnull((cast(y.listid as nvarchar(MAX))),'')
+''' and itemid=N'''+isnull((cast(y.itemid as nvarchar(MAX))),'')
+''''
,''
from ( select a.companycode as companycode,a.listid as listid,a.itemid as itemid,REPLACE(a.url,a.oldve,a.newve) as urlNew
from [ArisamKW].[dbo].[MIG_Items] as a,
ArisamDB.dbo.tb_Items as b
where a.companycode=b.CompanyCode
and a.listid=b.listid
and a.itemid=b.ItemID) as y
根据现有表获取插入脚本
SELECT TOP 10 'INSERT INTO [EntDA].[dbo].[tb_DeliveryAssets] VALUES (N'''+ CONVERT(nvarchar(36),[DeliveryID])+''''
+',N''' +isnull((cast([DeliveryAssetType] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([Title] as nvarchar(MAX))),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[ServiceCode])),'') +''''
+',N''' +isnull((cast([LanguageID] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([Countrys] as nvarchar(MAX))),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[ResCgyCode] )),'') +''''
+',N''' +isnull((cast([ProjectCode] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([ProjectName] as nvarchar(MAX))),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[FunctionCode] )),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[BizTypeCode] )),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[SolutionCode] )),'') +''''
+',N''' +isnull((cast([ItemType] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([OwnerDept] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([Contents] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([NamoKey] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([AttachType] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([Url] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([Title] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([SecurityLevel] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([ReadAuthXml] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([KMTag] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([KnowledgeCategory] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([Writer] as nvarchar(MAX))),'') +''''
+',N''' +isnull((convert(nvarchar(23),[WriteDate],21)),'') +''''
+',N''' +isnull((cast([LastEditor] as nvarchar(MAX))),'') +''''
+',N''' +isnull((convert(nvarchar(23),[LastEditDate],21)),'') +''''
+',N''' +isnull((cast([SaveState] as nvarchar(MAX))),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[ResourceCode])),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[CategoryCode] )),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[ActivityCode] )),'') +''''
+',N''' +isnull((CONVERT(nvarchar(36),[TaskCode])),'') +''''
+',N''' +isnull((cast([ReadCount] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([RecommandCode] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([IsMailSend] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([ICT_Service] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([Writer2] as nvarchar(MAX))),'') +''''
+',N''' +isnull((cast([LastEditor2] as nvarchar(MAX))),'') +''''
+',N''' +isnull((CONVERT(nvaRCHAR(36),[DeliveryID2] )),'') +''''
+',N''' +isnull((convert(nvarchar(23),[LastEditDate2],21)),'') +''')'
,''
FROM [EntDA].[dbo].[tb_DeliveryAssets]
相关文章
- 代码审计-thinkphp3.2.3框架漏洞sql注入
- JavaWeb_(Hibernate框架)Hibernate中数据查询语句SQL基本用法
- sql server简单查询
- 动态SQL应用小列子
- 利用SQL查询扶贫对象医保报销比率的审计方法
- 读书笔记--SQL必知必会14--组合查询
- sql基础--怎样查询所有列数据
- MongoDB查询和sql查询的总结
- mysql 5.7开启sql日志的配置
- Apache Spark源码走读(七)Standalone部署方式分析&sql的解析与执行
- 读书笔记--SQL必知必会21--使用游标
- 读书笔记--SQL必知必会20--管理事务处理
- 不得不看,只有专家才知道的17个SQL查询提速秘诀!
- 请教如何实现SQL查询24小时内,当前时间之前20分钟的信息
- Sql Server 查询表中各列名称、表中列数
- [转] 常用SQL查询语句
- Atitit mybatis的扩展使用sql udf,js java等语言 目录 1.1. 默认,mybatis使用xml,sql等语言来书写业务流程1 2. 使用ognl调用java函数1 3
- Atitit 补充说明 sql知识图谱与线路图attilax总结补充说明
- Database之SQLSever:SQL命令实现查询之多表查询、嵌套查询、分页复杂查询,删除表内重复记录数据、连接(join、left join和right join简介及其区别)等案例之详细攻略
- Database之SQL:SQL命令实现理解外键、约束(非空约束/唯一性约束/CHECK约束/主键约束/外键约束/查询约束)的概念及其相关案例之详细攻略
- 成功解决SQL server服务,远程过程调用失败
- 【编程实践】每个开发者都应该知道的数据库查询 SQL 命令
- SQL Server -减少代码触发的负担
- SQL随着子查询结果更新多个字段
- Dynamics CRM Microsoft SQL Server 指定的数据库具有更高的版本号
- sql sum嵌套查询+ group by
- 【大数据开发运维解决方案】Oracle Sql基础查询
- 【SQL开发实战技巧】系列(三十九):Oracle12C常用新特性☞新增分页查询
- 关于SQL优化的几点说明
- 从零实现ORM框架GeoORM-database/sql基础-01
- 6-4Table API and SQL的使用.