Friday 5 July 2019

Retrieving table records count from the SQL database

The steps to be followed as mentioned below:

1. Open SQL Server Management Studio (SSMS)

2. Connect to the database server (instance))

3. Go to database, Click “New Query” from SSMS menu tool bar


4. Copy the following SQL Query and Paste it to the “New Query” Window


SET NOCOUNT ON;
DECLARE @LastTableName VARCHAR(128)
SET @LastTableName = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME DESC)

SELECT 'SELECT ''' +  TABLE_NAME + ''' AS TableName, COUNT(1) AS  RecordCount FROM ' + TABLE_NAME +
CASE WHEN TABLE_NAME = @LastTableName THEN '' ELSE CHAR(13) +'UNION ALL ' END FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME
SET NOCOUNT OFF;


5. Click “Result to Text” button from SSMS menu tool bar(Previous button of “Result to Grid” button)

6. Execute the SQL query which you have pasted in the “New Query” Window

7. You will get the SQL Query for counting the table records of selected database in the Result section

8. Copy the full SQL query script which is in the Result section, paste it to the “New Query” Window and Execute it

9. You get the Record count for each table of selected database







Returning a value from Stored Procedure


CREATE PROCEDURE [dbo].[USP_PT_Convert_Integer]
(
       @InputString VARCHAR(10)
)
AS
BEGIN

       SET NOCOUNT ON;

       DECLARE @InputValue INT, @DotIndex TINYINT

       IF ISNUMERIC(@InputString) = 1
       BEGIN
             
              SET @DotIndex = CHARINDEX('.', @InputString)
             
              IF @DotIndex = 0
                     SET @DotIndex = LEN(@InputString)
              ELSE
                     SET @DotIndex = @DotIndex - 1
              SET @InputValue = CONVERT(INT,SUBSTRING(@InputString,1, @DotIndex))
       END

       RETURN @InputValue

       SET NOCOUNT OFF;
END

GO

CREATE PROCEDURE [dbo].[USP_PT_Convert_Integer_Caller]
(
       @InputString VARCHAR(10)
)
AS
BEGIN

       SET NOCOUNT ON;
      
       DECLARE @InputValue INT

       EXEC @InputValue = dbo.[USP_PT_Convert_Integer] @InputString

       SELECT @InputValue

       SET NOCOUNT OFF;
END

GO

----------------------------------------------

EXEC [dbo].[USP_PT_Convert_Integer_Caller] '23456.89088'

RESULT: 23456


EXEC [dbo].[USP_PT_Convert_Integer_Caller] '23456a.89088'

RESULT: 0

MS SQL Version, Instance and Compatibility Level, Installed Date


How to get/know the MS SQL Version?
SELECT @@VERSION;

How to get/know the MS SQL Instance?
SELECT @@SERVERNAME;

How to get/know the MS SQL Compatibility Level?
SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; 


How to change the MS SQL Compatibility Level?
ALTER DATABASE YourDatabaseName
SET COMPATIBILITY_LEVEL = 120;

   -- COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 } 

How to get/know the MS SQL Installed Date?

SELECT create_date AS InstalledDateTime

FROM sys.server_principals
WHERE sid = 0x010100000000000512000000

How to get/know the latest upgrade date of MS SQL Server? 
  • Go to Control Panel\All Control Panel Items\Programs and Features, locate "Microsoft SQL Server" , the 'installed on' field will show you the date when it was been installed