http://www.sql-ex.ru/exercises.php
Category: sql sever syntax
in order to get the name of a document and the project that contains it – I needed a self-join select d1.name as docname, d2.name as Project from stuff d1, stuff d2 where d1.ownerid = d2.id order by Project
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = ” SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””) WHILE @TableName IS NOT NULL BEGIN SET…
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema], T.[name] AS [table_name], AC.[name] AS [column_name], TY.[name] AS system_data_type, AC.[max_length], AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded], AC.[is_identity] FROM sys.tables AS T INNER JOIN sys.all_columns AC ON T.object_id = AC.object_id INNER JOIN sys.types TY ON AC.system_type_id = TY.system_type_id AND AC.user_type_id = TY.user_type_id ORDER BY…
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like ‘pre%’ )
USE yourdbname SELECT * FROM sys.Tables WHERE name like ‘%something%’ ORDER BY name
REVERSE(LEFT(REVERSE(@myString),CHARINDEX(‘>’,REVERSE(@myString))-1))
thanks goes out to the book ‘SQL Cookbook’ – Lucky Chapter 13 Hierarchical Queries … with x (tree,childid,depth) as ( select cast(name as varchar(500)), childid, 0 from vw_users_parent_children union all select cast(x.tree+’–>’+e.name as varchar(500)), e.childid, x.depth+1 from vw_users_parent_children e, x where x.childid = e.children_id )…