Skip to main content

Posts

Showing posts from April, 2021

Find column name in database sql server?

 /************************************/  /* find column name in database sql server? /*************************************/ /********* Search Tables:***********/ SELECT      c.name  AS 'ColumnName'             ,t.name AS 'TableName' FROM        sys.columns c JOIN        sys.tables  t   ON c.object_id = t.object_id WHERE       c.name LIKE '%MyName%' ORDER BY   TableName,ColumnName; /**** Search Tables and Views: *******/ SELECT      COLUMN_NAME AS 'ColumnName'             ,TABLE_NAME AS  'TableName' FROM        INFORMATION_SCHEMA.COLUMNS WHERE       COLUMN_NAME LIKE '%MyName%' ORDER BY    TableName             ,ColumnName;
Read more

What database/procedure is using a linked server?

 /* How can I tell what database/procedure is using a linked server? */ /* How to check if MS SQL Server, Linked Server object is in use */ /************** Example 1  *********************/ SELECT      Distinct      referenced_Server_name As LinkedServerName,     referenced_schema_name AS LinkedServerSchema,     referenced_database_name AS LinkedServerDB,     referenced_entity_name As LinkedServerTable,     OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer FROM sys.sql_expression_dependencies WHERE referenced_database_name IS NOT NULL And referenced_Server_name = 'Linked Server Name' /************** Example 2  *********************/ SELECT DISTINCT      referenced_Server_name AS LinkedServerName,     referenced_schema_name AS LinkedServerSchema,     referenced_database_name AS LinkedServerDB,     referenced_entity_name AS LinkedServerTable,   ...
Read more

Get all databases with MDF and LDF File Location

 /*********************************************************/ /* get all databases with MDF and LDF File Location */ SELECT     db.name AS DBName,     type_desc AS FileType,     Physical_Name AS Location FROM     sys.master_files mf INNER JOIN      sys.databases db ON db.database_id = mf.database_id /*******************************************************/
Read more

Find List stored procedures in SQL Server database

 /********************************************************************************************/ /*    Example 1: List stored procedures in SQL Server database */ /********************************************************************************************/ SELECT COUNT(*) FROM sys.procedures /********************************************************************************************/ /*    Example 2: List stored procedures in SQL Server database */ /********************************************************************************************/ SELECT COUNT(*) SPCOUNT    FROM INFORMATION_SCHEMA.ROUTINES  WHERE ROUTINE_TYPE='PROCEDURE' /********************************************************************************************/ /*    Example 3: List stored procedures in SQL Server database */ /********************************************************************************************/  select schema_name(obj.schema_id) as schema_name...
Read more

Quickly search for SQL database data and objects in SSMS

/*   1) How to quickly search for SQL database data and objects in SSMS   2) Searching for sys schemaviews */ DECLARE @searchString nvarchar( 50 ); SET @searchString = '@userId'; SELECT DISTINCT     s.name AS Schema_Name,  O.name AS Object_Name ,  C.text AS Object_Definition FROM syscomments C     INNER JOIN sys.objects O      ON C.id = O.object_id    INNER JOIN sys.schemas S      ON O.schema_id = S.schema_id WHERE C.text LIKE  '%' + @searchString + '%'      OR O.name LIKE '%' + @searchString + '%' ORDER BY Schema_name, Object_name;
Read more

Listing All Triggers in the SQL Server Database

 /*********************************************************************************/ /* Listing All Triggers in the SQL Server - SQL Server Tutorial */ /* Need to list all triggers in SQL Server database with table */ /* List triggers in SQL Server database - SQL Server Data */ /*********************************************************************************/ /*********************************************************************************/    /*Example 1*/ /*********************************************************************************/ SELECT    ServerName   = @@servername,    DatabaseName = db_name(),    SchemaName   = isnull( s.name, '' ),    TableName    = isnull( o.name, 'DDL Trigger' ),    TriggerName  = t.name,     Defininion   = object_definition( t.object_id ) FROM sys.triggers t    LEFT JOIN sys.all_objects o       ON t.pare...
Read more

find all trigger associated with SQL Table

Need to list all triggers in SQL Server database with table name and table's schema How To Find List All Triggers in SQL Server? The below T-SQL Query will help us to get the results: Example 1: To list all triggers in a SQL Server, you query data from the sys.triggers view:   SELECT      name,     is_instead_of_trigger     FROM      sys.triggers   WHERE        type = 'TR';   Example 2: select trg . name as trigger_name ,     schema_name ( tab . schema_id ) + '.' + tab . name as [table] ,     case                               when is_instead_of_trigger = 1                     ...
Read more