Find all the Tables who having a Primary Key

We have a database in that some of the tables have primary key. With the help of below we will get the list of tables.

SELECT T.name as 'Table with Primary Key',s.name as [Primary Key Name] FROM SYS.Tables T Inner Join sysobjects s ON s.parent_obj=t.object_id WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1 AND t.type = 'U' and s.xtype='PK'

See the output

 

In my database [test] having only one table which has Primary key.

  

With the help of below script those do not have primary key

SELECT T.name as 'Table without Primary Key' FROM SYS.Tables T WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0 AND t.type = 'U'

 See the output

 

Use below sql script to find the primary key column name

SELECT OBJECT_NAME(ic.OBJECT_ID) AS [Table Name], COL_NAME(ic.OBJECT_ID,ic.column_id) AS [Column Name],  i.name AS [Primary key Name] FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id WHERE i.is_primary_key = 1

See the output

  

Dr Edgar F. Codd rules of RDBMS

Rule 1: Information Rule

Rule 2: Guaranteed Access Rule

Rule 3: Systematic Treatment of NULL Values

Rule 4: Active Online Catalog

Rule 5: Comprehensive Data Sub-Language Rule

Rule 6: View Updating Rule

Rule 7: High-Level Insert, Update, and Delete Rule

Rule 8: Physical Data Independence

Rule 9: Logical Data Independence

Rule 10: Integrity Independence

Rule 11: Distribution Independence

Rule 12: Non-Subversion Rule

Format Function Sql Server

Suppose we want to convert date, Currency or Number with Culture, We used the convert function to do this in SQL Server 2008 and older version of SQL Server but in 2012 a new function introduced that is format function & it’s very useful to format the date and integer value.

Format () is one of the new built-in String Function introduced as a Part of Sql Server 2012. It returns the value formatted in the specified format using the optional culture parameter value. It is not a Sql Server native function instead it is .NET CLR dependent function.

Syntax

 

Parameter	Description
Value	Value to be formatted (Date, Currency or Number)
Format	This parameter specifies the format in which the value will be formatted.
Culture	This parameter is optional. It specifies the culture in which the value is formatted. If it is not specified then the language of the current session is used.( US,Hind,tamil,UK etc)

Note: It will return the nvarchar.

In other word we can say that the function FORMAT () accepts 3 parameters. The first parameter is the VALUE parameter where you pass the date value or numeric value. The second parameter is the.NET Framework format string. The format parameter is case sensitive. "D" doesn’t mean the same as "d".  The third parameter is the culture. This can be any culture supported by the .NET Framework.

Culture info read here  :

https://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo(v=vs.80).aspx

See the Example

Date Formatting

DECLARE@dateDATETIME=GETDATE() --Weekday date name, month name and the day with year SELECTFORMAT(@date,'D','en-US')AS[Full Date]; SELECTFORMAT(@date,'d','en-US')AS[Month/Day/Year]; --Display only the month name and date SELECTFORMAT(@date,'m','en-US')AS[Moanth and Date]; --Display long date and time --"tt" stands for two-letter representation of AM/PM and the result will be "AM" or "PM" SELECTFORMAT(@date,'yyyy/MM/ddhh:mm:sstt','en-US')AS[Long Date and Time]; --Display the millionths of a second SELECTFORMAT(@date,'h\:m\:ss\.ffffff','en-US')AS[Time with millionths os second];

See the Output

 

We can display the date and time with Culture. See below example

DECLARE@dateDATETIME=GETUTCDATE() SELECT@dateAS'UTCDate', FORMAT(@date,'D','en-US')AS[US Culture Date], FORMAT(@date,'D','en-IN')AS[Indian Culture Date], FORMAT(@date,'D','hi-IN')AS[Date in Hindi], FORMAT(@date,'D','ta-IN')AS[Date in Tamil] SELECT@dateAS'UTCDate', FORMAT(@date,'D','zh-CN')AS[Date in China], FORMAT(@date,'D','de')AS[Date in German], FORMAT(@date,'D','ur')AS[Date in Urdu], FORMAT(@date,'D','mr-IN')AS[Date in Marathi]

See the Output

 

Formatting with the NUMBERS

DECLARE@moneymoney='2500'; SELECTFORMAT(@money,'C')AS[Money]

See the output

  

Here, we are getting the currency symbol ‘$’ because my current locale language setting is en-us. I could also display the currency ‘$’ explicitly by using the culture parameter as shown below.

  

Formatting with the Numbers

DECLARE@NUMBERASNUMERIC(18,4)=102568.9589 Select@NUMBERas[NUMBER] ,FORMAT(@NUMBER,'0.00')as[with 2 decimal place] ,FORMAT(@NUMBER,'0.000')as[with 3 decimal place] ,FORMAT(@NUMBER,'##,##0.00')as[Formatted Result] ,FORMAT(@NUMBER,'0')as[No decimal]

See other example

DECLARE @MobileNumeber decimal(18,2)= 8888802459 Select @MobileNumeber as [Mobile Number] ,FORMAT(@MobileNumeber,'##-###-#####') ,FORMAT(@MobileNumeber,'(+91)-###-##-######')

Remove Trailing Character from Number Column in sql server

Sometime in age or weight column we are getting values years or KM , we need to remove the character and  displace the numeric value and we will do the manipulation on that.

I am creating a table

CREATE TABLE tblEmp (                 EmpNo int,                 Name varchar(50),                 EmpAddress varchar(100),                 Mobile varchar(10),                 EmpAge varchar(10),                 EmpHegiht varchar(10),                 Empweight varchar(10) )

Now I am inserting some records.

INSERT INTO tblEmp VALUES (1,'Bagesh Kumar','Pune','888880XXXX','28 years','163 cm','78.5 kg') INSERT INTO tblEmp VALUES (2,'Rajesh Kumar','Patna','888880XXXX','25 years','153 cm','68.5 kg') INSERT INTO tblEmp VALUES (3,'Umesh Kumar','Chennai','888880XXXX','22 years','177 cm','88.5 kg') INSERT INTO tblEmp VALUES (4,'Ganesh Kumar','Kanpur','888880XXXX','26','163 cm','50 kg') INSERT INTO tblEmp VALUES (5,'Ajit Kumar','Mumbai','888880XXXX','20 years','173 cm','75')

See the table value

 

Use below sql script

SELECT                  EmpNo,Name,EmpAddress,Mobile                 ,EmpAge                 ,LEFT(EmpAge,DATALENGTH(EmpAge)-(PATINDEX('%[0-9]%',REVERSE(EmpAge))-1)) AS [Age in Numeric],                 EmpHegiht,                 LEFT(EmpHegiht,DATALENGTH(EmpHegiht)-(PATINDEX('%[0-9]%',REVERSE(EmpHegiht))-1)) AS [Height in Numeric],                 Empweight,                 LEFT(Empweight,DATALENGTH(Empweight)-(PATINDEX('%[0-9]%',REVERSE(Empweight))-1)) AS [weight in Numeric] FROM tblEmp

See the output

 

PATINDEX

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

Find letters, numbers and special characters from a column in sql server

Suppose we have a table in that a column having mix dates (I mean it has letters, number and special character s). We need find out.

Suppose having the below data

DECLARE @table table (id int identity(1,1), value nvarchar(100)) INSERT INTO @table VALUES ('B65bfNM#*amF5*t'), ('4GZ_3h^2STJTZVG') , ('4%bKU&hxgj7!3!t'), ('uX=g3PE+w&uVV!h')

With the help of below sql script we will get it

DECLARE @table table (id int identity(1,1), value nvarchar(100)) INSERT INTO @table VALUES ('B65bfNM#*amF5*t'), ('4GZ_3h^2STJTZVG') , ('4%bKU&hxgj7!3!t'), ('uX=g3PE+w&uVV!h') SELECT ID,value,     (SELECT         SUBSTRING(value, number, 1)     FROM master..spt_values     WHERE SUBSTRING(value, number, 1) LIKE '[A-Z]' AND type = 'P'     FOR xml PATH ('')) as [Letters],                 (SELECT         SUBSTRING(value, number, 1)     FROM master..spt_values     WHERE SUBSTRING(value, number, 1) LIKE '[0-9]' AND type = 'P'     FOR xml PATH ('')) as [Numbers],                 (SELECT         SUBSTRING(value, number, 1)     FROM master..spt_values     WHERE SUBSTRING(value, number, 1) LIKE '[^0-9]'     AND SUBSTRING(value, number, 1) LIKE '[^A-Z]' AND type = 'P'     FOR xml PATH ('')) as [special characters] FROM @table

See the output