How do I find a stored procedure containing ?

SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%text%'
AND ROUTINE_TYPE='PROCEDURE'

 

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%text%'

SELECT  *
FROM sys.sql_modules
WHERE Definition LIKE '%text%'
AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

Identifying Currently running Sql quries with Host information

SELECT es.session_id, es.host_name, es.login_name , er.status, DB_NAME(database_id) AS DatabaseName , SUBSTRING (qt.text,(er.statement_start_offset/2) + 1, ((CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2) + 1) AS [Individual Query] , qt.text AS [Parent Query] , es.program_name, er.start_time, qp.query_plan , er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads , er.blocking_session_id, er.open_transaction_count, er.last_wait_type , er.percent_complete FROM sys.dm_exec_requests AS er INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp WHERE es.is_user_process=1 AND es.session_Id NOT IN (@@SPID) ORDER BY es.session_id

FOR XML PATH

droptable test

create table test (pos varchar(4), str varchar(10))

insert into test values ('1','hello'), ('1','yes'),('1','No') select * from test

SELECT stuff((SELECT ', ' + str FROM test WHERE pos = '1' FOR XML PATH('')),1,2,'')

Script to compare Data in Rows

drop table #t create table #t (id varchar(6) , [year] varchar(4), membertype varchar(4))insert into #t (id, [year], membertype)select
select
select
select
select
;WITH CTE1 AS(SELECT
)SELECT a.id,CASE WHEN a.membertype <> b.membertype THEN 'changed' ELSE 'not changed' END AS status,a.membertype AS current_membertype, b.membertype AS prev_member_type
FROM cte1 a
INNER JOIN cte1 b ON a.id = b.id WHERE a.year_rank = 1 AND b.year_rank = 2AND a.membertype <> b.membertype *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY YEAR DESC) AS year_rank FROM #t '123456', '2015', 'Yes' union '123456', '2009', 'No' '123457', '2016', 'No' union '123457', '2011', 'Yes' union '123456', '2010', 'Yes' union

Correct way of creating an extra column

select *
into newsysobjects
from sys.objects

alter table newsysobjects
add val varchar(30)

.
Another method is using SELECT...INTO with an explicit CAST like the example below

SELECT *, ISNULL(CAST('' AS varchar(30)), '') AS val
INTO dbo.newobjects
FROM sys.objects

how to find primary keys for particular server and particular Data Base

Particular Server :


EXEC

sp_primarykeys



Particular DB ::

select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY' 'servername' sp_Msforeachdb "SELECT * FROM ?.INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY'"

How to find row counts for all the tables in one DB

CREATE  TABLE #t 
(name SYSNAME, rows CHAR(11), reserved VARCHAR(18), data VARCHAR(18),index_size VARCHAR(18), unused VARCHAR(18))
EXEC sp_msforeachtable 'INSERT INTO #t EXEC sp_spaceused ''?'''

DMV

Audit Security Change Data Capture Related dm_audit_actions dm_audit_class_type_map Broker Service Broker dm_broker_activated_tasks dm_broker_connections dm_broker_forwarded_messages dm_broker_queue_monitors Cdc Change Data Capture dm_cdc_errors dm_cdc_log_scan_sessions Clr Common Language Runtime dm_clr_appdomains dm_clr_loaded_assemblies dm_clr_properties dm_clr_tasks Security *dm_cryptographic_provider_sessions *dm_cryptographic_provider_algorithms *dm_cryptographic_provider_keys dm_cryptographic_provider_properties dm_database_encryption_keys Database Database dm_db_file_space_usage dm_db_index_usage_stats dm_db_mirroring_auto_page_repair dm_db_mirroring_connections dm_db_mirroring_past_actions dm_db_missing_index_details dm_db_missing_index_groups dm_db_missing_index_group_stats dm_db_partition_stats dm_db_persisted_sku_features dm_db_script_level dm_db_session_space_usage dm_db_task_space_usage Exec Execution *dm_cached_plan_depent_objects * *dm_exec_plan_attributes *dm_exec_query_plan *dm_exec_query_stats dm_exec_sql_text *dm_exec_text_query_plan *dm_exec_xml_handles dm_exec_background_job_queue dm_exec_background_job_queue_stats dm_exec_cached_plans dm_exec_connections dm_exec_procedure_stats dm_exec_query_memory_grants dm_exec_query_optimizer_info dm_exec_query_resource_semaphores dm_exec_query_stats dm_exec_query_transformation_stats dm_exec_requests dm_exec_sessions dm_exec_trigger_stats Qn Query Notifications dm_qn_subscriptions Replication Replication dm_repl_articles dm_repl_schemas dm_repl_tranhash dm_repl_traninfo Resource govener Resource Govener dm_resource_governor_configuration dm_resource_governor_resource_pools dm_resource_governor_workload_groups dm_server_audit_status Transaction Transactional dm_tran_active_snapshot_database_transactions dm_tran_active_transactions dm_tran_commit_table dm_tran_current_snapshot dm_tran_current_transaction dm_tran_database_transactions dm_tran_locks dm_tran_session_transactions dm_tran_top_version_generators dm_tran_transactions_snapshot dm_tran_version_store XE Extended Events dm_xe_map_values dm_xe_objects dm_xe_object_columns dm_xe_packages dm_xe_sessions dm_xe_session_events dm_xe_session_event_actions dm_xe_session_object_columns dm_xe_session_targets

Filestream Filestreams dm_filestream_file_io_handles dm_filestream_file_io_requests Fts Full-Text Search dm_fts_active_catalogs dm_fts_fdhosts dm_fts_index_population dm_fts_memory_buffers dm_fts_memory_pools dm_fts_outstanding_batches dm_fts_population_ranges IO I/O *dm_io_cluster_shared_drives dm_io_backup_tapes dm_io_cluster_shared_drives dm_io_pending_io_requests OS Operating Systems dm_os_buffer_descriptors dm_os_child_instances dm_os_cluster_nodes dm_os_dispatchers dm_os_dispatcher_pools dm_os_hosts dm_os_latch_stats dm_os_loaded_modules dm_os_memory_allocations dm_os_memory_brokers dm_os_memory_cache_clock_hands dm_os_memory_cache_counters dm_os_memory_cache_entries dm_os_memory_cache_hash_tables dm_os_memory_clerks dm_os_memory_nodes dm_os_memory_node_access_stats dm_os_memory_objects dm_os_memory_pools dm_os_nodes dm_os_performance_counters dm_os_process_memory dm_os_ring_buffers dm_os_schedulers dm_os_spinlock_stats dm_os_stacks dm_os_sublatches dm_os_sys_info dm_os_sys_memory dm_os_tasks dm_os_threads dm_os_virtual_address_dump dm_os_waiting_tasks dm_os_wait_stats dm_os_workers dm_os_worker_local_storage