Retrieve the database objects contain a specific text string

SELECT DISTINCT so.name, so.xtype, sc.TEXT

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE sc.TEXT LIKE '%<<search text string>>%'

AND so.xtype IN ('TR', 'FN', 'P', 'V', 'IF', 'TF', 'FS', 'FT')

ORDER BY so.xtype, so.name

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

Application-Level Authentication strategies

Accessing Application using SQL Server Authentication,the Application username/password, credentials are encrypted and stored in database table, then check to see if those credentials are valid or not. 

Accessing APIs/Webservices, the username/password, credentials and Security keys are encrypted and stored in configuration files, then APIs/Webservices to validate those credentials are valid or not.

Calling/Connecting the 3rd Party APIs from Application, the 3rd party API username/password and Security keys are encrypted (Column Level Encryption) and stored in the database table using with TDE (Transparent Data Encryption) at the database level.

The 3rd Party APIs to validate those credentials are valid or not. Accessing the Application using Windows Authentication, the windows usernames are stored in database table, then check to see if those users are valid or not.

Database-Level Authentication strategies

Ø  Using SQL Server Authentication for connecting the SQL Server databases, the database credentials username/password, mapping databases and access permissions are set at the database-level.

Ø  The database username/password, then the SQL Server validates it to access the databases if those credentials are valid or not.

Ø  Valid/Authorized user to access the databases if database user has appropriate permissions for accessing the databases.

Ø  Using Windows Authentication for connecting the databases, the windows username, mapping databases and access permissions are set at the database-level.

Passing and Retrieving variable value from sp_executesql

1.       Create a Table (Emp) with required columns and data types

2.       Declare a Input Variable (@InEmpId) as INT

3.       Declare the required Output Variables (@OutEmpName) as VARCHAR(100) = '' and @OutEmpAge INT = 0)

4.       Define the Input String Variable (@InEmpId INT) Output and String Variables @OutEmpName VARCHAR(100) OUTPUT, @OutEmpAge INT OUTPUT pass it to sp_executesql as a first parameter definition

5.       Pass the input Variable (@InEmpId) and Output variables @OutEmpName = @OutEmpName OUTPUT, @OutEmpAge = @OutEmpAge OUTPUT to the sp_executesql as second parameter

EXAMPLE:

CREATE TABLE Emp

(

       EmpId INT IDENTITY(1,1) PRIMARY KEY,

       EmpName VARCHAR(100),

       EmpAge INT

)

GO -- REQUIRED for accessing the TYPE in next line

INSERT INTO Emp (EmpName, EmpAge)

       VALUES('Govindaraj Kanniappan',41)

INSERT INTO Emp (EmpName, EmpAge)

       VALUES('Kanniappan Govindaraj',42)

INSERT INTO Emp (EmpName, EmpAge)

       VALUES('Govindaraj K',43)

INSERT INTO Emp (EmpName, EmpAge)

       VALUES('K Govindaraj',44)

GO

DECLARE @InEmpId AS INT = 2

DECLARE @OutEmpName AS VARCHAR(100) = '', @OutEmpAge INT = 0

DECLARE @SQL NVARCHAR(400)=''

SET @SQL = 'SELECT @OutEmpName = EmpName, @OutEmpAge = EmpAge FROM Emp WITH (NOLOCK) 

WHERE EmpId = @InEmpId'

EXECUTE sp_executesql @SQL, N'@InEmpId INT, @OutEmpName VARCHAR(100) OUTPUT, @OutEmpAge INT OUTPUT', @InEmpId, @OutEmpName = @OutEmpName OUTPUT, @OutEmpAge = @OutEmpAge OUTPUT

SELECT @OutEmpName AS EmpName, @OutEmpAge AS Age