Credit: Joe Stefanelli on Stack Overflow I wanted a simple way to find triggers on a specific database, Joe Stefanelli posted SQL which did exactly what I wanted.
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid
INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
Credit: Waseemulla S on Microsoft Answers I had a USB device that I could not unload, I prefer the old dialog box anyway and found it is still possible to show this box in Windows 10. The forum post above has the answer, however according to someone was a very poor answer. For me the […]
We encountered an issue during a migration to Windows 10 where I had missed a program which had hard coded links, these no longer worked due to Microsoft Access being a newer version. I generated a simple script so we could change the hard coded links to allow it to select the correct version, all […]
I needed to generate a list of folders and their subfolders, I used a simple command for dir command and slapped into a text file. Below replaced with the folder you want to get the list from (i.e C:\Users\Default) and replace with where you want to save a textfile (i.e C:\textfile.txt).
dir /b /s /a:d "<Location>\*" > <TextFile>
Credit: Basit’s SQL Server Tips The following is very handy to check for any unused index’s on a MSSQL database, this should only be run once the database has been up for at least a week or when all scheculed jobs have been allowed to run at least once. The only change I made is […]
Find a stored procedure (or any text) inside a scheduled on on SQL Server 2008 and 2012 by querying the sysjobs and sysjobsteps system tables. The following provides the name of the job and the step name. It uses the standard % wildcard so replace myjob in “%myjob%” below
SELECT name, "description", "enabled", database_name,step_name,step_id,command
FROM "msdb"."dbo"."sysjobsteps" t1
INNER JOIN "msdb"."dbo"."sysjobs" t2 on t1.job_id = t2.job_id
where t1."command" LIKE '%myjob%'
More info on SQL system […]
We had an issue where a Kodak i160 scanner would appear when a Windows 7 machine booted but then disappear after the Kodak i160 scanner went to sleep. The Kodak scanner was using a Kofax Virtual Rescan driver, after some checking it turned out the machine was using a standard fire wire driver for the […]
Credits: SQL Server Central SQL Authority Microsoft Docs Microsoft Docs We recently had an issue where MSDB was growing very quickly, it turned out it was due to a huge increase in data we was directly emailing to users. We now run a weekly job just to maintain the MSDB email tables as whole copies […]
Credit: Microsoft TechNet On some occasions a few MS SQL databases have exhausted tempdb space, this was due to users leaving computers running for weeks on end and the program using different isolation levels or open transactions. I use the following in SQL in see which process ID have been running the longest, as its […]
On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many […]