Posts

Showing posts from 2019

Change web site name for IIS web app deploy in Azure Devops Pipeline

Image
Azure Dev Ops - Deploy to on-prem server and change name of the web si te  If you tried deploying you web app to on-prem server and you might have seen that the Web site name parameter seems to a linked parameter and not easy to change. By default if you choose to Deploy IIS template it adds IIS Web APP Mange and IIS Web App deploy. To fix this I had to remove these two and use Deploy IIS Website/APP task which has simply an option to change the Web site name.

SSMS pre register multiple server to avoid connecting it again and again

Image
I recently learned about the registered servers feature in SQL Server Management studio. Below are benefits I felt more useful, and you can see details on the link above. ·          Can register multiple servers at once then no need to use connect to server window all the time. ·          Can group the servers, it helps easily organize these servers ·          You can double click in registered server window to open it in object explorer window. ·          You can run a query to multiple servers in one step (Risky, so not recommended but in case it’s the need) Below is screen shot of my SSMS, I have created three groups DEV, TST, PROD and can connect to these server just by double clicking any of the servers . Even if you restart SSMS these registered servers are preserved.  There is a feature of import and e...

Search column names in database

Sometimes if you are working on maintenance application, you might need to search a specific column in a database. replace column name with you column name you are looking for and you should get the results SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '% %' ORDER BY schema_name, table_name;

Get when the tables are last used

 WITH LastActivity (ObjectID, LastAction) AS   (        SELECT object_id AS TableName,               last_user_seek as LastAction          FROM sys.dm_db_index_usage_stats u         WHERE database_id = db_id(db_name())         UNION        SELECT object_id AS TableName,               last_user_scan as LastAction          FROM sys.dm_db_index_usage_stats u         WHERE database_id = db_id(db_name())         UNION        SELECT object_id AS TableName,               last_user_lookup as LastAction          FROM sys.dm_db_index_usage_stats u         WHERE database_id = db_id(db_name())   )   SELECT OBJECT_NAME(so.object...

Find when a stored procedure was run last time

SELECT  a.execution_count ,     OBJECT_NAME(objectid) Name,     query_text = SUBSTRING(     b.text,     a.statement_start_offset/2,     ( CASE WHEN a.statement_end_offset = -1      THEN len(convert(nvarchar(max), b.text)) * 2      ELSE a.statement_end_offset      END - a.statement_start_offset)/2     ) ,     b.dbid ,     dbname = db_name(b.dbid) ,     b.objectid ,     a.creation_time,     a.last_execution_time,     a.* FROM      sys.dm_exec_query_stats a CROSS APPLY  sys.dm_exec_sql_text(a.sql_handle) as b WHERE OBJECT_NAME(objectid) = ORDER BY a.last_execution_time DESC

Find if a stored procedure is used in any job

SELECT j.name   FROM msdb.dbo.sysjobs AS j   WHERE EXISTS   (     SELECT 1 FROM msdb.dbo.sysjobsteps AS s       WHERE s.job_id = j.job_id       AND s.command LIKE '% %'   );