I just want to call out couple of points: Use code generators You can’t have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation … Read more
Using SetTriggerOrder is fine, but if your code depends on a specific sequence of execution, why not wrap all your triggers into stored procedures, and have the first one call the second, the second call the third, etc. Then you simply have the first one execute in the trigger. Someone in the future will be … Read more
You’re darn close – you need to use two dots in your check: IF OBJECT_ID(‘tempdb..#tmpTable1′) IS NOT NULL ** | use two dots here! Basically, this is saying: check in the tempDB and I don’t care what schema the table is in As Joe rightfully said: this is not 100% correct: it doesn’t check in … Read more
without Dynamic SQL each option it’s clause for example: ORDER BY case when @var1 = ‘priority asc’ THEN priority END ASC , case when @var1 = ‘priority desc’ then priority end DESC, case when @var2 = ‘report_date asc’ then report_date end ASC, case when @var2 = ‘report_date desc’ then report_date end DESC
To expand on James Allen’s answer: SELECT d.name, last_user_seek = MAX(last_user_seek), last_user_scan = MAX(last_user_scan), last_user_lookup = MAX(last_user_lookup), last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i JOIN sys.databases AS d ON i.database_id=d.database_id GROUP BY d.name Use this modified version if you don’t want results per database context and wish to include the database name at the beginning … Read more
Disclamer This is just some additional information that might help anyone. I want to make it abundantly clear that what I am describing here is possibly: A. not 100% correct and B. not safe in terms of network security. I am not a DBA, but every time I find myself setting up a SQL Server … Read more
If your dates are no more than 2047 days apart: declare @dt datetime, @dtEnd datetime set @dt = getdate() set @dtEnd = dateadd(day, 100, @dt) select dateadd(day, number, @dt) from (select number from master.dbo.spt_values where [type] = ‘P’ ) n where dateadd(day, number, @dt) < @dtEnd I updated my answer after several requests to do … Read more
SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE objectproperty(object_id,’IsProcedure’) = 1 AND definition like ‘%Foo%’
Your database connection can be configured to encrypt traffic and to accept any certificate from your server. Not a grand solution, but it worked for me. The resulting connection string should look like this: “[…];Encrypt=True;TrustServerCertificate=True”
Try this script here: DECLARE @DatabaseCollation VARCHAR(100) SELECT @DatabaseCollation = collation_name FROM sys.databases WHERE database_id = DB_ID() SELECT @DatabaseCollation ‘Default database collation’ SELECT t.Name ‘Table Name’, c.name ‘Col Name’, ty.name ‘Type Name’, c.max_length, c.collation_name, c.is_nullable FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id WHERE … Read more