[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]

巴特西