Happy Holidays in T-SQL style

Want to know the result of the following query? All you need to do is goto SSMS, set the result mode to Text (Press CTRL+T) and then execute the following code

set nocount on select space(17-len(replicate(char(182),no)))+ replicate(char(182),no*2-1)from (select top 10 row_number() over (order by (select 1)) as no from (select 0 as no union all select 0 union all select 0) as t1 cross join (select 0 as no union all select 0 union all select 0) as t2) as t union all select space(14)+replicate(char(124),5) union all select space(10)+cast(0x486170707920486F6C6964617973 as varchar(100))

Advertisements

Different methods to know structure of a table

There are many methods to know the structure of a table. Here are some of them

1 In Query Analyser type the name of the table, highlight it and press Alt+F1

2 Generate SQL Script option from Enterprise Manager/Management Studio

3 Use sp_help system stored procedure

EXEC sp_help 'table_name'

4 Use sp_columns system stored procedure

EXEC sp_columns 'table_name'

5 Use information_schema.columns catelogue view

SELECT 
	* 
FROM 
	INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='table_name'

Find out tables with Primary key

One of the questions asked in the forum is “How do I find out the tables that have primary key defined?”

Well. There can be several methods. These are two among them

Let us create the following tables in tempdb database

use tempdb

create table test1
(
id int primary key,
name varchar(100)
)
GO
create table test2
(
id int ,
name varchar(100)
)

Method 1 : Query on INFORMATION_SCHEMA.TABLE_CONSTRAINTS system catalog view

SELECT
	table_name
FROM
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
	CONSTRAINT_TYPE='PRIMARY KEY'

Method 2 : Query on SYS.TABLES system table

SELECT
	name AS table_name
FROM
	SYS.TABLES
WHERE
	OBJECTPROPERTY(OBJECT_ID,'TABLEHASPRIMARYKEY') = 1

Both the queries return the following result

table_name
--------------
test1

Question of the month November 2016 – Why does semicolon followed by GO give error?

You know that specifying a number after GO statement will execute the above statement by as many number as specified in it.

Example

select 1
go 2

As you see you will get two result sets with value 1

But when you put semicolon after go 2

select 1
go 2;

You get the following error

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing go.

Why does it give error?