These simple snippets of code will show all triggers in the current database, along with the tables they belong to. Unfortunately the INFORMATION_SCHEMA views do not show triggers so we need to revert to using less documented options. The proper way would be to use the INFORMATION_SCHEMA.TRIGGERS view, however SQL Server has yet to create and publish this view. In the meantime we need to use one of the following workarounds: [cc lang=”sql”] SELECT table_name = so.name ,trigger_name = st.name ,trigger_text = sc.text ,create_date = st.create_date FROM sys.triggers st JOIN sysobjects so ON st.parent_id = so.id JOIN syscomments sc ON sc.id = st.[object_id] [/cc] You can also use only sysobjects: [cc lang=”sql”] SELECT table_name = so2.name ,trigger_name = so.name ,trigger_text = sc.text ,create_date = so.crdate FROM sysobjects so JOIN sysobjects so2 ON so.parent_obj = so2.id JOIN syscomments sc ON sc.id = so.id WHERE so.type = ‘tr’ [/cc] I would imagine that SQL Server will provide more documented ways to query this information, until then we need to take a chance by deploying these solutions.
Continue reading ...
Occasionally, there comes a requirement to search all databases on a particular server for either columns with a specific name, or columns relating to a specific subject. In the most recent case, I had to find all similar columns in all databases because the company plans to change the datatype of these columns. My task began with having to locate each of these columns in order to determine the impact of the change, and what actually needed to change. For this, I used the undocumented sp_MSForEachDB procedure. It is a great procedure that actually takes a SQL string as an input parameter. The SQL used for the input parameter must then contain a question mark (?) in which each database replaces the question mark and the SQL is executed. Here is an example: [cc lang=”sql”] sp_MSForEachDB @command1=’USE ?; SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE Data_Type = ”int” AND COLUMN_NAME like ”%ColumnNameHere%”’ [/cc] To utilize correctly, replace the ColumnNameHere with the name of the column you want to find (or what the column name should be like). It will then return you all the columns on the server that are named like the column you specified.
Continue reading ...
The following SQL Server script works to determine if a particular job is currently running. This feature is nice to have if you do not want jobs to step over one another. I frequently will use this script as the first step of the job. If it is currently running, then the step will raise an error and not proceed to the next step. [cc lang=”sql”] sp_configure ‘Ad Hoc Distributed Queries’,1 RECONFIGURE WITH OVERRIDE GO DECLARE @ExecutionStatus INT DECLARE @JobName varchar(100) = ‘%yourjobnamehere%’ SELECT name, current_execution_status, job_id INTO #Jobs FROM OPENROWSET(‘SQLNCLI’, ‘server=(local);trusted_connection=yes’, ‘set fmtonly off exec msdb.dbo.sp_help_job’) SELECT @ExecutionStatus = current_execution_status FROM #Jobs WHERE job_id = (SELECT sj.job_id FROM msdb.dbo.sysjobs sj WHERE name like @JobName) IF @ExecutionStatus != 1 BEGIN RAISERROR (‘Job Is Not Running’, 16, 1 ) END [/cc] The only caveat to this method, which is the only one I know, is that you need to enable ‘Ad Hoc Distributed Queries’. While I don’t consider this a harmful change, it may not be allowed unless you have dbo permissions on the database.
Continue reading ...
This seems to be the easiest way to kill all connections (sessions) on a SQL Server database: [cc lang=”sql”] ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE [/cc] After executing the above, it will put the database in single user mode. So you then need to remove single user mode using: [cc lang=”sql”] EXEC sp_dboption ‘database_name’, ‘single user’, ‘FALSE’ [/cc]
Continue reading ...
This script will automatically determine whether a rebuild or a reorganize should be used according to the fragmentation of the index. It will then execute the appropriate command. Note that performing index rebuilds online during production hours will cause contention. [cc lang=”sql”] SET NOCOUNT ON; DECLARE @objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint; DECLARE @schemaname nvarchar(258); DECLARE @objectname nvarchar(258); DECLARE @indexname nvarchar(258); DECLARE @partitionnum bigint; DECLARE @partitions bigint; DECLARE @frag float; DECLARE @command varchar(8000); — ensure the temporary table does not exist IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘work’) DROP TABLE work; — conditionally select from the function, converting object and index IDs — to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO work FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’) WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; — Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM work; — Open the cursor. OPEN partitions; — Loop through the partitions. FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; WHILE @@FETCH_STATUS = 0 BEGIN; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o JOIN sys.schemas as s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count (*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; — 30 is the decision point at which to switch — between […]
Continue reading ...
