Get Logical File Name Of Database

USE DatabaseName
GO
EXEC sp_helpfile
GO

What is the Initial Size of Temp Db ?

SELECT  NAME ,
        SIZE * 8.0 / 1024 'Current Size In MB'
FROM    tempdb.sys.database_files;

SELECT  NAME ,
        SIZE * 8.0 / 1024 'Initial Size in MB'
FROM    master.sys.sysaltfiles
WHERE   dbid = 2;  

Return Output Parameter From Stored Procedure

CREATE PROCEDURE [dbo].[USP_FindName]
      @ID INT,
      @NAME VARCHAR(30) OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
     
      SELECT @NAME = NAME FROM dbo.TABLENAME
      WHERE ID = @ID
END

Disable The Windows Authentication Mode Of SQL Server

ALTER LOGIN [ADMIN\SQL2014] DISABLE
GO

Detach Multiple Database In Sql Server

USE MASTER 
DECLARE DATABASES CURSOR
FOR
    SELECT  NAME
    FROM    SYSDATABASES
    WHERE   NAME NOT IN ( 'MASTER', 'TEMPDB', 'MSDB', 'MODEL' ) 
DECLARE @DB SYSNAME 

DECLARE @SQL VARCHAR(5000) 
OPEN DATABASES 

FETCH NEXT FROM DATABASES INTO @DB 
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = 'SP_DETACH_DB ' + @DB 
        EXEC (@SQL) 
        FETCH NEXT FROM DATABASES INTO @DB 

    END 
CLOSE DATABASES 
DEALLOCATE DATABASES  

Find Row Count Of Tables Based On Cursor

DECLARE @TName VARCHAR(MAX); 
DECLARE @Cnt VARCHAR(MAX); 
DECLARE TABLENAME CURSOR
FOR
    SELECT  TNAME
    FROM    TABLENAME
    WHERE   TYPE = 'L'; 

OPEN TABLENAME; 

FETCH NEXT FROM TABLENAME INTO @TName; 

WHILE @@FETCH_STATUS = 0
    BEGIN 
        SET NOCOUNT ON; 

        SELECT  @Cnt = COUNT(*)
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE   TABLE_NAME = @TName; 

        PRINT 'Table : ' + @TName + ' (' + @Cnt + ')'; 

        FETCH NEXT FROM TABLENAME INTO @TName; 
    END;

CLOSE TABLENAME; 
DEALLOCATE TABLENAME; 

Working With The TRY…CATCH Block in Sql Server

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