Query to display names of all columns and data types in a table - SQL

Following is the query that returns column name and data type in a table in SQL:

Replace the string 'Table Name' with the actual table name

select first.name[Column Name],

case when second.name IN ('char', 'varchar', 'nchar', 'nvarchar') then second.name + 

' (' + convert(nvarchar(10),first.max_length ) + ')' 

when second.name IN ('decimal','numeric') then second.name + ' (' + convert( nvarchar(10),first.precision ) + 

', ' + convert( nvarchar(10),first.scale ) + ')'

else second.name end [Data Type],

case first.is_nullable when 1 then 'Yes' else 'No' end [Allow Nulls]

from sys.columns first

inner join sys.types second on first.system_type_id=second.system_type_id

where object_name(first.object_id)='Table Name'

order by first.column_id

Population by Country - Tableau

Learn About Tableau

Ranking Functions in SQL Server with example

There are four types of Ranking Functions in SQL. All the Ranking Functions are used with Over Clause

• Row_Number: The row_number function gives each a row in a table a serial number. 

Example: 

Consider below Table named 'EmpDetails', the table contains two columns 'EmpID' and 'EmpName'

  

Now we will implement Row_number to the above table:

Query:

select *, ROW_NUMBER() over (order by empid) as Rowno from dbo.EmpDetails 

The output of the query is as follows:

• Rank: The Rank function gives rank to each rows in the result based on the over clause.

Example:

select *, Rank() over (order by empid) as Rowno from dbo.EmpDetails 

Ouput:

As seen above, the employees with empid as '1' has been given Rank 1, whereas the employee with empid '2' has rank 3, because there were two employees with empid 1 that has rank one.

• Dense_Rank: This function is similar to Rank() function, the only difference is that it gives Rank without any gaps.

Example:

select *, dense_Rank() over (order by empid) as Rowno from dbo.EmpDetails 

Output:

If you observe the above result set, dense_rank() function has assigned rank 2 to the employees with Empid 2, unlike Rank() function which ranked them as 3.

• Ntile: The Ntile function requires to specify a number in the parentheses and based on that the it divides the rows in the result set.

Example:

select *, Ntile(2) over (order by empid) as Rowno from dbo.EmpDetails 

Here, we have specified 2 in the parentheses, hence the result set with contain only two ranks i.e. 1 and 2

Output:

  

Social Media Usage Comparison - Tableau

Learn About Tableau

AskReddit Stats

Learn About Tableau

Olympic Medal Details using Tableau

Learn About Tableau

Barbeque (Barbecue) Chart to represent revenue on quarterly basis using Tableau

Learn About Tableau

Flight details dashboard using Tableau

Learn About Tableau