xivSolutions

SELECT SUBSTRING('00008815208', PATINDEX('%[^0]%', '00008815208'+'.'), LEN('00008815208'))

xivSolutions

git branch --merged master | grep -v 'master$' | xargs git branch -d

xivSolutions

select 
    i.name as IndexName, 
    o.name as TableName, 
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded, 
    co.[name] as ColumnName
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id 
    and ic.index_id = i.index_id

xivSolutions

(SET search_path).+?(?=SET search_path)|(SET search_path).+?$

xivSolutions

var request = new RestRequest("/job/feature/api/json/config.xml", Method.GET);
request.AddHeader("content-type", "application/json");
var client = new RestClient(config.JenkinsUrl)
{
    Authenticator = new HttpBasicAuthenticator(config.UserName, config.Password)
};
var result =  client.Execute(request);

xivSolutions

SELECT t.table_schema AS schema, t.table_name AS name, 
  (SELECT json_agg(keys) from (
    SELECT c.column_name, c.data_type, c.character_maximum_length AS char_length, 
      c.column_default IS NOT NULL AS is_auto
    FROM information_schema.columns c
    LEFT OUTER JOIN information_schema.key_column_usage kcu 
      ON c.table_schema = kcu.constraint_schema AND c.table_name = kcu.table_name AND c.column_name = kcu.column_name
    LEFT OUTER JOIN information_schema.table_constraints tc
      ON kcu.constraint_schema = tc.constraint_schema AND kcu.constraint_name = tc.constraint_name
    WHERE 

xivSolutions

SELECT t.table_schema AS schema, t.table_name AS name, 
  (SELECT json_agg(keys) from (
    SELECT c.column_name, c.data_type, c.character_maximim_length AS char_length, 
      c.column_default IS NOT NULL AS is_auto
    FROM information_schema.columns c
    LEFT OUTER JOIN information_schema.key_column_usage kcu 
      ON c.table_schema = kcu.constraint_schema AND c.table_name = kcu.table_name AND c.column_name = kcu.column_name
    LEFT OUTER JOIN information_schema.table_constraints tc
      ON kcu.constraint_schema = tc.constraint_schema AND kcu.constraint_name = tc.constraint_name
    WHERE 

xivSolutions

DEALLOCATE tables;
PREPARE tables(text, text, text) AS
SELECT row_to_json (tbls) AS tables FROM (
	SELECT t.table_schema, t.table_name, 
		(SELECT json_agg(keys) from (
			SELECT c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH AS char_length, 
				c.column_default IS NOT NULL AS is_auto
			FROM information_schema.columns c
			LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
				ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME

xivSolutions

deallocate tables;
prepare tables(text, text, text) as
SELECT c.TABLE_NAME, c.COLUMN_NAME, kcu.CONSTRAINT_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, tc.CONSTRAINT_TYPE, 
	CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, 
	c.column_default is not null as is_auto,
	c.column_default
FROM information_schema.columns c
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
	ON c.TABLE_SCHEMA = kcu.CONSTRAINT_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

xivSolutions

-- deallocate tables;
prepare tables(text, text, text) as
SELECT c.TABLE_NAME, c.COLUMN_NAME, kcu.CONSTRAINT_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, tc.CONSTRAINT_TYPE, 
	CASE tc.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN CAST(1 AS BIt) ELSE CAST(0 AS Bit) END AS IsPrimaryKey, 
	case (
	case left(c.column_default, 13) when 'uuid_generate' then 1 else 0 end +
	case left(c.column_default, 7) when 'nextval' then 1 else 0 end)
	when 0 then false else true end AS IsAuto, 
	c.column_default
FROM information_schema.columns c