Working With The TRY…CATCH Block

DECLARE @COLUMNID int,
        @COLUMNNAME int

BEGIN
  BEGIN TRY
    BEGIN TRANSACTION
      UPDATE TABLENAME
      SET COLUMNNAME = @COLUMNNAME
      WHERE COLUMNID = @COLUMNID
    COMMIT TRANSACTION
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION
    DECLARE @ErrorNumber int = ERROR_NUMBER()
    DECLARE @ErrorLine int = ERROR_LINE()
    DECLARE @ErrorMessage nvarchar(4000) = ERROR_MESSAGE()
    DECLARE @ErrorSeverity int = ERROR_SEVERITY()
    DECLARE @ErrorState int = ERROR_STATE()

    PRINT 'Actual Error Number: ' + CAST(@ErrorNumber AS varchar(10))
    PRINT 'Actual Line Number: ' + CAST(@ErrorLine AS varchar(10))

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
  END CATCH
END
GO

Return Custom Error Message

DECLARE @CustomErrorString varchar(100),
              @Value int = 5
IF @Value >= 5
BEGIN

  SET @CustomErrorString = 'Value Is Greater Than or Equal To ' + CONVERT(varchar(10), @Value)

  RAISERROR (@CustomErrorString, 10, 1, 50, 50);
  RETURN --Exit Now
END

Cross - Database Dependencies

SELECT  OBJECT_NAME (REFERENCING_ID) AS REFERENCING_OBJECT,

REFERENCED_DATABASE_NAME,

REFERENCED_SCHEMA_NAME, REFERENCED_ENTITY_NAME

FROM SYS.SQL_EXPRESSION_DEPENDENCIES

WHERE 1 = 1

AND REFERENCED_DATABASE_NAME IS NOT NULL

AND IS_AMBIGUOUS = 0

  

Creating Query Shortcuts in SQL Server Management Studio

You can create shortcuts for commonly used queries in SQL Management Studio. Some of the shortcuts are predefined, like  "Alt + F1" will execute "sp_help" and "Ctrl + 1" will execute "sp_who". You can create new shortcuts for your own queries.

To create a new shortcut,

Go to Tools > Options > Environment > Keyboard > Query Shortcuts

For example, you can create a shortcut for:

Syntax

"Ctrl + 3 " : SELECT * FROM  (single space after from keyword)

    

Find Data Base Table With Primary Key

SELECT  Tab.TABLE_NAME AS TABLE_NAME,
  COLUMN_NAME AS PRIMARY_KEY_COLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS Tab
  ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
  AND TC.CONSTRAINT_NAME = Tab.CONSTRAINT_NAME
  AND Tab.TABLE_NAME = 'TABLE'
ORDER BY Tab.TABLE_NAME,
Tab.ORDINAL_POSITION;

Get Autoclose and Autoshrink Values for the Databases in Sql Server

select databasepropertyex('yourdb','isautoshrink')


You can change it with the ALTER DATABASE statement


ALTER DATABASE yourdb

SET AUTO_SHRINK OFF | ON

How to Find In Valid Object Name SQL Server

SELECT   STORED_PROCEDURE = QUOTENAME(SCHEMA_NAME(O.[SCHEMA_ID])) + '.' + QUOTENAME(O.NAME),
  OBJ_TYPE = O.TYPE_DESC,

  D.REFERENCED_DATABASE_NAME,s

  D.REFERENCED_SCHEMA_NAME,
  D.REFERENCED_ENTITY_NAME
FROM SYS.SQL_EXPRESSION_DEPENDENCIES D
JOIN SYS.OBJECTS O
  ON D.REFERENCING_ID = O.[OBJECT_ID]
WHERE D.IS_AMBIGUOUS = 0
AND D.REFERENCED_ID IS NULL
AND D.REFERENCED_SERVER_NAME IS NULL -- IGNORE OBJECTS FROM LINKED SERVER
AND CASE D.REFERENCED_CLASS -- IF DOESN’T EXIST
  WHEN 1 -- OBJECT
  THEN OBJECT_ID(
    ISNULL(QUOTENAME(D.REFERENCED_DATABASE_NAME), DB_NAME()) + '.' +
    ISNULL(QUOTENAME(D.REFERENCED_SCHEMA_NAME), SCHEMA_NAME()) + '.' +
    QUOTENAME(D.REFERENCED_ENTITY_NAME))
  WHEN 6 -- OR USER DATATYPE
  THEN TYPE_ID(
    ISNULL(D.REFERENCED_SCHEMA_NAME, SCHEMA_NAME()) + '.' + D.REFERENCED_ENTITY_NAME)
  WHEN 10 -- OR XML SCHEMA
  THEN (SELECT
      1
    FROM SYS.XML_SCHEMA_COLLECTIONS X
    WHERE X.NAME = D.REFERENCED_ENTITY_NAME
    AND X.[SCHEMA_ID] = ISNULL(SCHEMA_ID(D.REFERENCED_SCHEMA_NAME), SCHEMA_ID()))
END IS NULL