Posts

Typing Speed Test

How to use this app?   Click on "Start Test"  Type Sample text in the text area To finish test click on "Show Result"  This will display the test result  Click on "Start Test" to try again  How to change sample text?   Click on "F5" near to help button.   It will generate random sample text for typing test  Privacy Note:   We are not saving any user information for this app.

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 '% %'   );