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))
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?