Find a specific column from the database objects

There are many ways to find out a specific column from the database objects like Tables and stored procedures.

1) Red Gate's SQL Search:

This tool will be integrated into SQL Server Management Studio once installed. This will help in searching across multiple object types and multiple databases.

2) Using a query:
The following query will find the tables and stored procedures that contain a certain field.  

SELECT sysobjects.name FROM syscolumns
LEFT JOIN sysobjects ON sysobjects.id = syscolumns.id
WHERE syscolumns.name like '%EmployeeID%'
ORDER BY 1

3) Using a query:
The following query will find the tables (only) that contain a certain field

SELECT * FROM information_schema.columns
WHERE
column_name LIKE '%EmployeeID%'

Status of SQL Server Agent

SQL Server Agent is a Microsoft Windows service that executes scheduled jobs and DBAs need to ensure the Agent is running. There are several methods to know the status of the SQL Server Agent.

1) Check if the SQL Server Agent is running in Services.

    Control panel --> Windows Administrative Tasks --> Services

2) In SSMS (SQL Server Management Studio), go to Object Explorer, scroll down to find "SQL Server Agent". Right-click and check the properties.

3) By executing the following query

SELECT [servicename], [status_desc]
FROM   sys.dm_server_services
WHERE  [servicename] LIKE N'%SQL Server Agent%';

4) Also, another query to see if there are any processes in "sysprocesses" system table.

SELECT * FROM sysprocesses
WHERE program_name LIKE N'SQLAgent%'

5) Another query to find out if SQL Server Agent's status

EXEC master.dbo.xp_servicecontrol 'QueryState', 'SQLServerAgent';
GO

6) Open the Microsoft SQL Server Configuration Manager from Configuration Tools and see the status of SQL Agent service. 

 

Please do let me know if you find any other method.

Server Principals Login Create and Modification Date

The certificate-based logins that start and end with double hash marks are stated below -

##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_PolicySigningCertificate##
##MS_SmoExtendedSigningCertificate##
##MS_AgentSigningCertificate##

By default, they are Windows-based logins and are enabled. If you want to look into both windows and SQL based logins, query against sys.server_principals as mentioned below.

USE master
GO
SELECT * FROM sys.server_principals
WHERE  name LIKE '%##%'

If you look into sys.server_principals for the certificate-based logins, you might confuse with the "create date" and "modify date" of those logins. The logins were created when SQL Server is installed however the dates are different than the installation date.

These logins will be dropped and recreated during the 'script level upgrade' which usually takes place after cumulative updates/patches or an unexpected restart of the active SQL node.

There won't be any change in the dates (since no logins will be recreated) when the services are restarted or routine failover takes place.

If you need more information on Server Principals, please click here.

Certificate Based Server Logins - SQL Server

The SQL and Windows logins information will be available in sys.server_principals along with the logins that were created from certificates when SQL Server is installed. However, these certificate-based logins are for internal system use only and will not be used to login to the system.

As per the documentation, these principal accounts do not have passwords that can be changed by administrators as they are based on certificates issued to Microsoft.

Use the below query to identify those logins.

SELECT * FROM sys.server_principals
WHERE  name LIKE '%##%'
              AND is_disabled = 0

If you want to know all the logins that have double hash marks (##), use the below query.

If you see there are 8 principals that are created by SQL Server for both SQL and Windows logins and based on configuration SQL based logins were disabled. As I stated earlier, these logins cannot be used to login to SQL Server instance since they do not have access. Please check below -

USE master
GO

SELECT a.name, a.is_disabled, b.hasaccess FROM sys.server_principals a
JOIN sys.syslogins b ON a.name = b.name
WHERE a.name LIKE '%##%'

The login ##MS_AgentSigningCertificate## is not disabled and has access to the system however one cannot impersonate a login mapped to a certificate/asymmetric key. 

These certificates are either be used as permission containers or to encrypt the data. If you want to see what permissions they have, use the below query-

SELECT a.name principal_name, b.class_desc, b.permission_name
FROM sys.server_permissions b
JOIN sys.server_principals a ON b.grantee_principal_id = a.principal_id
WHERE name LIKE '%Certificate%'
ORDER BY a.name

Please note that these are created by the system, and it should be not be deleted.

Have you ever observed the creation date and modify date for these certificate-based logins?

Click here to know more.

Difference between Local File System vs HDFS

In an operating system, file system is the strategy that will be used to keep track of files on a disk. It has its own method to organize the files on the disk or partition. 

HDFS will be deployed on top of the existing Operating system to bring its own file system method. This way, the system will have two different file systems at a time which we call "local file system" and HDFS.

There is a difference between the Local File System and the Hadoop Distributed File System (HDFS) and the difference is mainly because of the block size. 

The block size is 4 KB both in Windows and Unix local file systems. But the block size in Hadoop HDFS is 64 MB in the initial version and in later versions, it is 128 MB which is configurable. This impacts the disk seek. For a large file, there will be multiple disk-seeks in local file system due to its 4KB block size. Since HDFS maintains higher block allocation, the data will be read sequentially after every individual seek. 

Large files will be split into multiple chunks automatically, distributed and stored across various slave machines (aka. nodes) in HDFS. In the local file system, the files will be saved the way they are.

The machine with the local file system is physically a single unit. HDFS is logically a single unit.

These are the differences I found over a period of time. If you find any more, please do share with me. 

Hijri Date in SQL Server - with Islamic Month Name

This article focuses on converting the Gregorian date into an Islamic date along with Islamic month name in textual format. In SQL Server there are two methods to convert the Gregorian date to Islamic date, however, for Islamic month name we need to depend on our own code.

Either you can write a function or a stored procedure or you can simply put the month names in your query's conditional clauses.

First, let me show you the methods, SQL Server supports -

SELECT GETDATE() AS [GregorianDate],

       CONVERT(VARCHAR(23),GETDATE(),131) AS [Hijri date]

GO

SELECT GETDATE() AS [GregorianDate], 

       FORMAT(GETDATE(),'yyyy-MM-dd hh:mm:ss','ar') AS [Hijri date]

GO

In case you need to display the Islamic date in text format like "11 al-Muḥarram 1442" -

SELECT

CONVERT(VARCHAR(2), DATEPART(DAY, FORMAT(GETDATE(),'yyyy-MM-dd','ar'))) + ' ' +

CONVERT(NVARCHAR(30), CASE (DATEPART(MONTH, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

    WHEN 1 THEN N'al-Muḥarram'

    WHEN 2 THEN N'Ṣafar'

    WHEN 3 THEN N'Rabīʿ al-ʾAwwal'

    WHEN 4 THEN N'Rabīʿ ath-Thānī'

    WHEN 5 THEN N'Jumādā al-ʾAwwal'

    WHEN 6 THEN N'Jumādā ath-Thāniyah'

    WHEN 7 THEN N'Rajab'

    WHEN 8 THEN N'Shaʿbān'

    WHEN 9 THEN N'Ramaḍān'

    WHEN 10 THEN N'Shawwāl'

    WHEN 11 THEN N'Zū al-Qaʿdah'

    WHEN 12 THEN N'Zū al-Ḥijjah'

    END) + ' ' +

CONVERT(VARCHAR(4), DATEPART(YEAR, FORMAT(GETDATE(),'yyyy-MM-dd','ar')))

GO

Do let me know if you find any other way. 

Moving a table from one Hive database to another

This article is to let you know how to move a table from one database to another in Hive.

CREATE TABLE TestDB.emp 
AS
SELECT * FROM dbTest.emp;

The above statement will create a table (named "emp") in the database in which you want to move the table and import the data from the old one. You can keep your old table in the old database or you can simply drop it.

If you do not want to use the "create" statement, then you have a choice to alter the table location as mentioned below.

ALTER TABLE TestDB.emp RENAME TO dbTest.emp;

Hope it helps..!!

XML in SQL Server - Part-2

In the previous part, we learned how to use the default modes and how we can change the shapes of the XML model using the same available modes. We will learn how to read the XML file that is generated by SQL Server.

SELECT VideoID, VideoDesc, vcVideoLocation
FROM VideoData

FOR XML RAW;

Once you execute your query, you will see the results in the result-pane, which can be saved as CSV file, however, it shouldn’t be the right way to save XML files that generated by SQL Server.

Click on the result from the result-pane. The entire XML document gets opened in a new window. Go to File and select “Save As”. It will prompt you the file-directory to save the file based on your choice.

Once the file is saved, open a new query window and type the below command –

SELECT CAST(XMLData AS XML)
FROM OPENROWSET(
       BULK 'D:\MyDocuments\blog posts\XML-SQLServer\test.xml'

       , SINGLE_BLOB) AS X(XMLData)

Please do let me know if you need more clarification.