Thursday, July 17, 2025

MS SQL useful queries

MS SQL useful queries:


----------------------------------------------------------------
-- Find currently running jobs
SELECT
    j.name AS JobName,
    ja.job_id,
    ja.start_execution_date,
    ja.session_id,
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) AS RunningMinutes
FROM msdb.dbo.sysjobactivity ja
INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.stop_execution_date IS NULL
    AND ja.start_execution_date IS NOT NULL
----------------------------------------------------------------
-- Get detailed job execution information
SELECT
    j.name AS JobName,
    j.job_id,
    ja.start_execution_date,
    ja.session_id,
    CASE ja.last_executed_step_id
        WHEN 0 THEN 'Job is currently running'
        ELSE 'Job completed or failed'
    END AS Status
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id
WHERE ja.session_id = (
    SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity
    WHERE job_id = j.job_id
)
AND ja.stop_execution_date IS NULL
----------------------------------------------------------------
-- Stop a job by name
EXEC msdb.dbo.sp_stop_job @job_name = 'YourJobName'
----------------------------------------------------------------
-- Stop a job by job_id
EXEC msdb.dbo.sp_stop_job @job_id = 'Your-Job-ID-Here'
----------------------------------------------------------------
-- Stop all currently running jobs
DECLARE @JobName NVARCHAR(128)
DECLARE job_cursor CURSOR FOR
    SELECT j.name
    FROM msdb.dbo.sysjobactivity ja
    INNER JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
    WHERE ja.stop_execution_date IS NULL
        AND ja.start_execution_date IS NOT NULL

OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @JobName

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_stop_job @job_name = @JobName
    PRINT 'Stopped job: ' + @JobName
    FETCH NEXT FROM job_cursor INTO @JobName
END

CLOSE job_cursor
DEALLOCATE job_cursor
----------------------------------------------------------------
--Here both source and target table exist
INSERT
INTO TargetTbl  
SELECT * FROM SourceTbl
----------------------------------------------------------------
--Here source table exist, and target table not exist
SELECT *
INTO TargetTbl
FROM SourceTbl;
----------------------------------------------------------------
--Find Database Routines Using Custom Table Type
--This query searches for stored procedures, functions, and other database routines
that reference a specific user-defined table type in their definition.
SELECT ROUTINE_NAME  
FROM INFORMATION_SCHEMA.ROUTINES  
WHERE ROUTINE_DEFINITION LIKE '%MyTableType%';  
----------------------------------------------------------------
--Find Object Dependencies Using sp_depends
--This system stored procedure identifies all database objects that depend on or are
depended upon by the specified table.
EXEC sp_depends 'MyTable1';
----------------------------------------------------------------
--Database Schema Analysis Query - Complete Table and Column Metadata with Constraints
--Purpose: Retrieves comprehensive metadata for all database tables and views,
including detailed column information and constraint relationships.
SELECT
    t.TABLE_SCHEMA, t.TABLE_TYPE, t.TABLE_NAME,
    c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, c.COLUMN_DEFAULT,
    kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_NAME = c.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON c.TABLE_NAME = kcu.TABLE_NAME
    AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
ORDER BY t.TABLE_SCHEMA,t.TABLE_TYPE, t.TABLE_NAME, c.ORDINAL_POSITION
----------------------------------------------------------------
--Database Schema Analysis Query - Retrieves complete metadata for all database
objects (tables, functions, procedures, table types, views) with columns and
constraints in specified order
-- Get all tables and their columns with constraints
SELECT
    'TABLE' as ObjectType,
    1 as ObjectTypeOrder,
    t.TABLE_SCHEMA as SchemaName,
    t.TABLE_NAME as ObjectName,
    c.COLUMN_NAME as ColumnName,
    c.DATA_TYPE as DataType,
    c.IS_NULLABLE,
    c.COLUMN_DEFAULT,
    c.ORDINAL_POSITION,
    kcu.CONSTRAINT_NAME,
    tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.TABLE_NAME = kcu.TABLE_NAME
    AND c.COLUMN_NAME = kcu.COLUMN_NAME
    AND c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
    AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
WHERE t.TABLE_TYPE = 'BASE TABLE'

UNION ALL

-- Get functions and their parameters
SELECT
    'FUNCTION' as ObjectType,
    2 as ObjectTypeOrder,
    r.ROUTINE_SCHEMA as SchemaName,
    r.ROUTINE_NAME as ObjectName,
    ISNULL(p.PARAMETER_NAME, 'NO_PARAMETERS') as ColumnName,
    ISNULL(p.DATA_TYPE, 'N/A') as DataType,
    'YES' as IS_NULLABLE,
    NULL as COLUMN_DEFAULT,
    ISNULL(p.ORDINAL_POSITION, 0) as ORDINAL_POSITION,
    NULL as CONSTRAINT_NAME,
    NULL as CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.ROUTINE_NAME = p.SPECIFIC_NAME
    AND r.ROUTINE_SCHEMA = p.SPECIFIC_SCHEMA
WHERE r.ROUTINE_TYPE = 'FUNCTION'

UNION ALL

-- Get stored procedures and their parameters
SELECT
    'PROCEDURE' as ObjectType,
    3 as ObjectTypeOrder,
    r.ROUTINE_SCHEMA as SchemaName,
    r.ROUTINE_NAME as ObjectName,
    ISNULL(p.PARAMETER_NAME, 'NO_PARAMETERS') as ColumnName,
    ISNULL(p.DATA_TYPE, 'N/A') as DataType,
    'YES' as IS_NULLABLE,
    NULL as COLUMN_DEFAULT,
    ISNULL(p.ORDINAL_POSITION, 0) as ORDINAL_POSITION,
    NULL as CONSTRAINT_NAME,
    NULL as CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.ROUTINES r
LEFT JOIN INFORMATION_SCHEMA.PARAMETERS p ON r.ROUTINE_NAME = p.SPECIFIC_NAME
    AND r.ROUTINE_SCHEMA = p.SPECIFIC_SCHEMA
WHERE r.ROUTINE_TYPE = 'PROCEDURE'

UNION ALL

-- Get user-defined table types and their columns
SELECT
    'TABLE_TYPE' as ObjectType,
    4 as ObjectTypeOrder,
    SCHEMA_NAME(tt.schema_id) as SchemaName,
    tt.name as ObjectName,
    c.name as ColumnName,
    TYPE_NAME(c.system_type_id) as DataType,
    CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END as IS_NULLABLE,
    CASE
        WHEN c.default_object_id > 0 THEN
            (SELECT definition FROM sys.default_constraints WHERE object_id = c.default_object_id)
        ELSE NULL
    END as COLUMN_DEFAULT,
    c.column_id as ORDINAL_POSITION,
    NULL as CONSTRAINT_NAME,
    NULL as CONSTRAINT_TYPE
FROM sys.table_types tt
JOIN sys.columns c ON tt.type_table_object_id = c.object_id
WHERE tt.is_user_defined = 1

UNION ALL

-- Get all views and their columns with constraints
SELECT
    'VIEW' as ObjectType,
    5 as ObjectTypeOrder,
    t.TABLE_SCHEMA as SchemaName,
    t.TABLE_NAME as ObjectName,
    c.COLUMN_NAME as ColumnName,
    c.DATA_TYPE as DataType,
    c.IS_NULLABLE,
    c.COLUMN_DEFAULT,
    c.ORDINAL_POSITION,
    kcu.CONSTRAINT_NAME,
    tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME
    AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.TABLE_NAME = kcu.TABLE_NAME
    AND c.COLUMN_NAME = kcu.COLUMN_NAME
    AND c.TABLE_SCHEMA = kcu.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
    AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
WHERE t.TABLE_TYPE = 'VIEW'

ORDER BY
    ObjectTypeOrder,
    SchemaName,
    ObjectName,
    ORDINAL_POSITION
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------




Wednesday, July 16, 2025

Create a chat room by using SignalR Service

Create a chat room by using SignalR Service
https://learn.microsoft.com/en-us/azure/azure-signalr/signalr-quickstart-dotnet-core#prerequisites

dotnet new web
dotnet user-secrets init
dotnet user-secrets set Azure:SignalR:ConnectionString "<Your connection string>"
dotnet add package Microsoft.Azure.SignalR
dotnet run
http://localhost:5258

Create a Azure SignalR service.

Folder structure:

Program.cs:
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddSignalR().AddAzureSignalR();
// builder.Services.AddSignalR().AddAzureSignalR("<connection_string>");
var app = builder.Build();

app.UseDefaultFiles();
app.UseRouting();
app.UseStaticFiles();
app.MapHub<ChatSampleHub>("/chat");
app.Run();

ChatSampleHub.cs:
using Microsoft.AspNetCore.SignalR;

public class ChatSampleHub : Hub
{
    public Task BroadcastMessage(string name, string message) =>
        Clients.All.SendAsync("broadcastMessage", name, message);

    public Task Echo(string name, string message) =>
        Clients.Client(Context.ConnectionId).SendAsync("echo", name,         $"{message} (echo from server)");
}

index.html:
<!DOCTYPE html>
<html>
<head>
  <meta http-equiv="Cache-Control" content="no-cache, no-store, must-revalidate" />
  <meta name="viewport" content="width=device-width">
  <meta http-equiv="Pragma" content="no-cache" />
  <meta http-equiv="Expires" content="0" />
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@3.3.7/dist/css/bootstrap.min.css"
    rel="stylesheet" />
  <link href="css/site.css" rel="stylesheet" />
  <title>Azure SignalR Group Chat</title>
</head>
<body>
  <h2 class="text-center" style="margin-top: 0; padding-top: 30px; padding-bottom:
    30px;">Azure SignalR Group Chat</h2>
  <div class="container" style="height: calc(100% - 110px);">
    <div id="messages" style="background-color: whitesmoke; "></div>
    <div style="width: 100%; border-left-style: ridge; border-right-style: ridge;">
      <textarea id="message" style="width: 100%; padding: 5px 10px; border-style:
    hidden;"
        placeholder="Type message and press Enter to send..."></textarea>
    </div>
    <div style="overflow: auto; border-style: ridge; border-top-style: hidden;">
      <button class="btn-warning pull-right" id="echo">Echo</button>
      <button class="btn-success pull-right" id="sendmessage">Send</button>
    </div>
  </div>
  <div class="modal alert alert-danger fade" id="myModal" tabindex="-1" role="dialog"
    aria-labelledby="myModalLabel">
    <div class="modal-dialog" role="document">
      <div class="modal-content">
        <div class="modal-header">
          <div>Connection Error...</div>
          <div><strong style="font-size: 1.5em;">Hit Refresh/F5</strong> to rejoin. ;)
        </div>
        </div>
      </div>
    </div>
  </div>

  <!--Reference the SignalR library. -->
  <script src="https://cdnjs.cloudflare.com/ajax/libs/microsoft-signalr/6.0.1/
    signalr.js"></script>

  <!--Add script to update the page and send messages.-->
  <script type="text/javascript">
    document.addEventListener("DOMContentLoaded", function () {
      function getUserName() {
        function generateRandomName() {
          return Math.random().toString(36).substring(2, 10);
        }

        // Get the user name and store it to prepend to messages.
        var username = generateRandomName();
        var promptMessage = "Enter your name:";
        do {
          username = prompt(promptMessage, username);
          if (!username || username.startsWith("_") || username.indexOf("<") > -1 ||
        username.indexOf(">") > -1) {
            username = "";
            promptMessage = "Invalid input. Enter your name:";
          }
        } while (!username)
        return username;
      }

      username = getUserName();
      // Set initial focus to message input box.
      var messageInput = document.getElementById("message");
      messageInput.focus();

      function createMessageEntry(encodedName, encodedMsg) {
        var entry = document.createElement("div");
        entry.classList.add("message-entry");
        if (encodedName === "_SYSTEM_") {
          entry.innerHTML = encodedMsg;
          entry.classList.add("text-center");
          entry.classList.add("system-message");
        } else if (encodedName === "_BROADCAST_") {
          entry.classList.add("text-center");
          entry.innerHTML = `<div class="text-center broadcast-message">${encodedMsg}
        </div>`;
        } else if (encodedName === username) {
          entry.innerHTML = `<div class="message-avatar pull-right">${encodedName}
        </div>` +
            `<div class="message-content pull-right">${encodedMsg}<div>`;
        } else {
          entry.innerHTML = `<div class="message-avatar pull-left">${encodedName}
        </div>` +
            `<div class="message-content pull-left">${encodedMsg}<div>`;
        }
        return entry;
      }

      function appendMessage(encodedName, encodedMsg) {
        var messageEntry = createMessageEntry(encodedName, encodedMsg);
        var messageBox = document.getElementById("messages");
        messageBox.appendChild(messageEntry);
        messageBox.scrollTop = messageBox.scrollHeight;
      }

      function bindConnectionMessage(connection) {
        var messageCallback = function (name, message) {
          if (!message) return;
          // Html encode display name and message.
          var encodedName = name;
          var encodedMsg = message.replace(/&/g, "&amp;").replace(/</g, "&lt;")
        .replace(/>/g, "&gt;");
          appendMessage(encodedName, encodedMsg);
        };
        // Create a function that the hub can call to broadcast messages.
        connection.on("broadcastMessage", messageCallback);
        connection.on("echo", messageCallback);
        connection.onclose(onConnectionError);
      }

      function onConnected(connection) {
        console.log("connection started");
        connection.send("broadcastMessage", "_SYSTEM_", username + " JOINED");
        document.getElementById("sendmessage").addEventListener("click",
        function (event) {
          // Call the broadcastMessage method on the hub.
          if (messageInput.value) {
            connection.send("broadcastMessage", username, messageInput.value)
              .catch((e) => appendMessage("_BROADCAST_", e.message));
          }

          // Clear text box and reset focus for next comment.
          messageInput.value = "";
          messageInput.focus();
          event.preventDefault();
        });
        document.getElementById("message").addEventListener("keypress",
        function (event) {
          if (event.keyCode === 13) {
            event.preventDefault();
            document.getElementById("sendmessage").click();
            return false;
          }
        });
        document.getElementById("echo").addEventListener("click", function (event) {
          // Call the echo method on the hub.
          connection.send("echo", username, messageInput.value);

          // Clear text box and reset focus for next comment.
          messageInput.value = "";
          messageInput.focus();
          event.preventDefault();
        });
      }

      function onConnectionError(error) {
        if (error && error.message) {
          console.error(error.message);
        }
        var modal = document.getElementById("myModal");
        modal.classList.add("in");
        modal.style = "display: block;";
      }

      var connection = new signalR.HubConnectionBuilder()
        .withUrl("/chat")
        .build();
      bindConnectionMessage(connection);
      connection.start()
        .then(function () {
          onConnected(connection);
        })
        .catch(function (error) {
          console.error(error.message);
        });
    });
  </script>
</body>
</html>

site.css:
/*html, body {
    font-size: 16px;
}

@media all and (max-device-width: 720px) {
    html, body {
        font-size: 20px;
    }
}*/

html, body {
    padding: 0;
    height: 100%;
}

#messages {
    width: 100%;
    border: 1px solid #ccc;
    height: calc(100% - 120px);
    float: none;
    margin: 0px auto;
    padding-left: 0px;
    overflow-y: auto;
}

textarea:focus {
    outline: none !important;
}

.system-message {
    background: #87CEFA;
}

.broadcast-message {
    display: inline-block;
    background: yellow;
    margin: auto;
    padding: 5px 10px;
}

.message-entry {
    overflow: auto;
    margin: 8px 0;
}

.message-avatar {
    display: inline-block;
    padding: 10px;
    max-width: 8em;
    word-wrap: break-word;
}

.message-content {
    display: inline-block;
    background-color: #b2e281;
    padding: 10px;
    margin: 0 0.5em;
    max-width: calc(60%);
    word-wrap: break-word;
}

.message-content.pull-left:before {
    width: 0;
    height: 0;
    display: inline-block;
    float: left;
    border-top: 10px solid transparent;
    border-bottom: 10px solid transparent;
    border-right: 10px solid #b2e281;
    margin: 15px 0;
}

.message-content.pull-right:after {
    width: 0;
    height: 0;
    display: inline-block;
    float: right;
    border-top: 10px solid transparent;
    border-bottom: 10px solid transparent;
    border-left: 10px solid #b2e281;
    margin: 15px 0;
}


OutPut:



Featured Post

MS SQL useful queries

MS SQL useful queries: ---------------------------------------------------------------- -- Find currently running jobs SELECT     j.name AS...

Popular posts