SQL cheatsheet: snippets and reference material

Here are a few of the SQL statements I keep googling for. I thought it would be helpful to keep the in one place, so I’ll have a cheatsheet next time.

Get a list of all tables in the database

1
2
3
SELECT * FROM sys.Tables
--OR
EXEC sp_tables '%', '%', 'master', "'TABLE'"

Get a list of all tables, views and system tables in the database

1
EXEC sp_tables '%'

Get a list of tables that the view depends on

1
2
3
4
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE View_Name = '<giveViewName>'
ORDER BY view_name, table_name

Get a list of all views that depend on a given table

1
2
3
4
SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE Table_Name= 'Address'
ORDER BY view_name, table_name

Get al list of all functions

1
2
3
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE upper(ROUTINE_TYPE) = 'FUNCTION'

Get information about all fields in a certain table

1
2
3
SELECT column_name, data_type,  is_nullable, character_maximum_length
FROM information_schema.columns
WHERE table_name='contents';

Get a list of tables with their create and modified dates

1
2
SELECT name, object_id, create_date, modify_date
FROM sys.tables;

Get a list of constraints and the fields they apply to for a given table.

1
2
3
4
5
6
SELECT a.table_name,  a.constraint_name, b.column_name,  a.constraint_type
FROM information_schema.table_constraints a, information_schema.key_column_usage b
WHERE a.table_name = 'contents'
AND  a.table_name = b.table_name
AND  a.table_schema = b.table_schema
AND  a.constraint_name = b.constraint_name;

Example of how to generate a script for all tables

1
2
3
SELECT 'SELECT count(1) FROM <a class="incipient" title="[click to create page]"' + 
' href="https://somelink/%27%20+%20table_name%20+%20%27">' + table_name + '</a>;'
FROM information_schema.tables;

Get the definition (code) of a user defined stored procedure

1
2
SELECT routine_definition FROM INFORMATION_SCHEMA.ROUTINES
WHERE specific_name = ‘USP_NAME'

Retrieve the collation of a column

1
2
3
4
SELECT
col.name, col.collation_name
FROM sys.columns col
WHERE object_id = OBJECT_ID('TableName')

Solve collation issues in a join, union, …

1
2
 ALTER TABLE tablename ALTER COLUMN id
COLLATE Latin1_General_CI_AS NOT NULL

Retrieve FK en PK information

1
sp_help ‘TABLENAME’

​Check whick document types can be searched with a full text search.

uncomment the “WHERE” part to check for a specific document type (in this case pdf) ​

1
2
3
SELECT document_type, path 
FROM sys.fulltext_document_types 
---WHERE document_type = '.pdf'

Another way of finding references between tables

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT s1.name as FROM_schema
, o1.Name as FROM_table
, s2.name as to_schema
, o2.Name as to_table
FROM sys.foreign_keys fk
INNER JOIN sys.objects o1
ON fk.parent_object_id = o1.object_id
INNER JOIN sys.schemas s1
ON o1.schema_id = s1.schema_id
INNER JOIN sys.objects o2
ON fk.referenced_object_id = o2.object_id
INNER JOIN sys.schemas s2
ON o2.schema_id = s2.schema_id
--For the purposes of finding dependency hierarchy
-- we're not worried about self-referencing tables
WHERE NOT(s1.name = s2.name AND o1.name = o2.name)

A word on collation

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive. Database, Tables and columns with different collation SQL Server 2000 allows the users to create databases, tables and columns in different collations.

source

Removing tags FROM text

The following example shows a way you can remove tags FROM text. In this case only the content of a couple of tags needed to be retrieved FROM the text.

Full example and problem description can be found here

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
declare @var nvarchar(max)
declare @tag nvarchar(max)
declare @label nvarchar(max)
declare @start int
declare @stop int
declare @len int
declare @needed int

set @var = '<Name>Example1</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<Prompt>Start Date (DD-MMM-YYYY)</Prompt>
<PromptUser>True</PromptUser>
<Parameter> </Parameter>
<Name>Example2</Name>
<Type>String</Type>
<Nullable>False</Nullable>
<AllowBlank>False</AllowBlank>
<Prompt>Case (Enter Case Number, % for all, OR %AL% for Alberta)</Prompt>
<PromptUser>True</PromptUser>
<DefaultValues>
<Value>%al%</Value>
</DefaultValues>
<Values>
<Value>%al%</Value>
</Values>
<Parameter></Parameter>'


set @needed = 0
set @start = charindex('<',@var)
set @stop = charindex('>',@var)
set @len = @stop - @start +1
set @tag = substring(@var,@start,@len)
set @label = substring(@var,@start+1,@len-2)
set @var = replace(@var,@tag,@label + ' : ')

while(@start <> 0)
begin
set @start = charindex('<',@var)
set @stop = charindex('>',@var)
set @len = @stop - @start +1
if(@start <> 0)
begin
set @tag = substring(@var,@start,@len)
if(charindex('/',@tag) = 0)
begin
set @label = substring(@var,@start+1,@len-2)+ ' : '
if(lower(@label) <> 'name : ' and lower(@label) <> 'value : ' and lower(@label) <> 'prompt : ')
begin
set @needed = 0
set @var = replace(@var,@tag,'')
set @start = @stop - len(@tag)
set @stop = charindex('<',@var)
set @len = @stop - @start
set @tag = substring(@var,@start,@len)
set @var = replace(@var,@tag,'')
end
end
else
begin
set @label = ''
end
set @var = replace(@var,@tag,@label)
end
end
print replace(@var,'
','')