SQL Server queries all stored procedure information, triggers, and indexes

SQLServer no selectPr_Nameas [Stored Procedure], [Parameter] stuff (select, + [Parameter] from (selectPr. nameasPr_Name, parameter. name ++ Type. name + (+ convert (varchar (32), parameter. max_length) +) asParam

SQL Server queries all stored procedure information, triggers, and indexes without the select Pr_Name as [Stored Procedure], [Parameter] = stuff (select ',' + [Parameter] from (select Pr. name as Pr_Name, parameter. name + ''+ Type. name + '(' + convert (varchar (32), parameter. max_length) + ')' as Param

SQL Server queries all stored procedure information, triggers, and indexes <无>

Select Pr_Name as [Stored Procedure], [Parameter] = stuff (select ',' + [Parameter] from (select Pr. name as Pr_Name, parameter. name + ''+ Type. name + '(' + convert (varchar (32), parameter. max_length) + ')' as Parameterfrom sys. procedures Pr left joinsys. parameters parameter on Pr. object_id = parameter. object_idinner join sys. types Type on parameter. system_type_id = Type. system_type_idwhere type = 'P') t where Pr_Name = tb. pr_Name for xml path (''), 1, 1,'') from (select Pr. name as Pr_Name, parameter. name + ''+ Type. name + '(' + convert (varchar (32), parameter. max_length) + ')' as Parameterfrom sys. procedures Pr left joinsys. parameters parameter on Pr. object_id = parameter. object_idinner join sys. types Type on parameter. system_type_id = Type. system_type_idwhere type = 'P') tbwhere Pr_Name not like 'SP _ % '-- and Pr_Name not like 'dt %' group by Pr_Nameorder by Pr_Name

Select triggers. name as [Trigger], tables. name as [Table name], triggers. is_disabled as [disabled], triggers. is_instead_of_trigger AS [Trigger type], case when triggers. is_instead_of_trigger = 1 then 'instead OF 'when triggers. is_instead_of_trigger = 0 then' AFTER 'else nullend as [Trigger type description] from sys. triggers triggersinner join sys. tables on triggers. parent_id = tables. object_idwhere triggers. type = 'tr 'order by triggers. create_date

Select indexs. tab_Name as [Table name], indexs. index_Name as [index name], indexs. [Co_Names] as [index column], stored as [primary key], Ind_Attribute.is_unique AS [unique key], Ind_Attribute.is_disabled AS [disabled] from (select Tab_Name, Index_Name, [Co_Names] = stuff (select ',' + [Co_Name] from (select tab. name as Tab_Name, ind. name as Index_Name, Col. name as Co_Name from sys. indexes indinner join sys. tables tab on ind. object_id = tab. object_id and ind. type in (1, 2) inner join sys. index_columns on tab. object_id = index_columns.object_id and ind. index_id = index_columns.index_idinner join sys. columns Col on tab. object_id = Col. object_id and index_columns.column_id = Col. column_id) t where Tab_Name = tb. tab_Name and Index_Name = tb. index_Name for xml path (''), 1, 1,'') from (select tab. name as Tab_Name, ind. name as Index_Name, Col. name as Co_Name from sys. indexes indinner join sys. tables tab on ind. object_id = tab. object_id and ind. type in (1, 2) inner join sys. index_columns on tab. object_id = index_columns.object_id and ind. index_id = index_columns.index_idinner join sys. columns Col on tab. object_id = Col. object_id and index_columns.column_id = Col. column_id) tbwhere Tab_Name not like 'sys % 'group by Tab_Name, Index_Name) indexs inner join sys. indexes Ind_Attribute on indexs. index_Name = Ind_Attribute.nameorder by indexs. tab_Name

DECLARE @ s VARCHAR (4000), @ n INT, @ I INT, @ s1 VARCHAR (100) SELECT IDENTITY (INT) id, text INTO # FROM syscommentsSELECT @ n = @ ROWCOUNT, @ I = 0 WHILE @ I <@ nBEGINSELECT @ I = @ I + 1, @ s = ''SELECT @ s1 = REPLACE (RTRIM (LTRIM (STUFF (text, CHARINDEX ('as', text), 40000,''), 1, CHARINDEX ('proc', STUFF (text, CHARINDEX ('as', text), 40000, '') + 4,''), CHAR (10 ), ''), CHAR (13),'') FROM # where id = RTRIM (@ I) -- SELECT @ s1, ASCII (SUBSTR ING (@ s1, 3, 1) -- select len (REPLACE (@ s1, CHAR (13), ''), CHAR (10 ),'')) SELECT @ s = 'select text FROM tempdb. dbo. # where id = '+ RTRIM (@ I) EXEC ('exec master .. xp_mongoshell ''bcp "'+ @ s +'" queryout "e: \ ProcTXT \ '+ @ s1 + '.txt "-S" Hangzhou home \ SQLEXPRESS "-c-U" sa "-P" bd5178 "''') enddrop table # -- how do I identify line breaks ??? Some non-stored procedure objects SELECT top 10 text FROM syscomments where id in (select object_id from sys. procedures where type = 'P') SELECT text FROM syscomments where id in (select object_id from sys. procedures where type = 'P') and charindex ('allstock', text)> 0and charindex ('create PROCEDURE ', text)> 0

SELECT TEXT FROM syscomments WHERE id=object_id('SP_NAME')SP_HELPTEXT 'SP_NAME'

USE [your_database_name_here];GOSELECT * FROM sys.all_objectsWHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC') AND [is_ms_shipped] = 0 ORDER BY [name];GOSELECT * FROM sysobjects where type='P'