Facilitador de Scripts – MSSQL

 Alguns scripts para facilitar a programação PL/SQL em MSSQL:

–ABRE COLUNAS DA TABELA sem ALIAS

select LOWER(T.COLUMN_NAME)+’,’  from INFORMATION_SCHEMA.COLUMNS T
WHERE table_name = ‘[TABELA]‘
ORDER BY ordinal_position
–ABRE COLUNAS DA TABELA com ALIAS
select ‘[alias].’+LOWER(T.COLUMN_NAME)+’,’  from INFORMATION_SCHEMA.COLUMNS T
WHERE table_name = ‘[TABELA]‘
ORDER BY ordinal_position
– ABRE declaração de VARIAVEIS PARA COLUNAS DE UMA TABELA (FETCH DE UM CURSOR)
select ‘@fc_’+LOWER(T.COLUMN_NAME) + ‘ ‘+DATA_TYPE + CASE WHEN UPPER(DATA_TYPE) = ‘CHAR’ OR UPPER(DATA_TYPE) = ‘VARCHAR’ THEN ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+’)’ ELSE ” END+’,’  from INFORMATION_SCHEMA.COLUMNS T
WHERE table_name = ‘[TABELA]‘
ORDER BY ordinal_position
– PARA O INTO DO FETCH DO CURSOR.
select ‘@fc_’+LOWER(T.COLUMN_NAME)+’,’   from INFORMATION_SCHEMA.COLUMNS T
WHERE table_name = ‘[TABELA]‘
ORDER BY ordinal_position
– ABRE declaração de VARIAVEIS PARA COLUNAS DE UMA TABELA (novos valores)
select ‘@new_’+LOWER(T.COLUMN_NAME) + ‘ ‘+DATA_TYPE + CASE WHEN UPPER(DATA_TYPE) = ‘CHAR’ OR UPPER(DATA_TYPE) = ‘VARCHAR’ THEN ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+’)’ ELSE ” END+’,’  from INFORMATION_SCHEMA.COLUMNS T
WHERE table_name = ‘[TABELA]‘
ORDER BY ordinal_position
– ABRE CAMPOS COM VALOR DEFAULT PARA INSERT
select LEFT(case T.DATA_TYPE
when ‘VARCHAR’ then
CHAR(39) + ‘ ‘ + CHAR(39)
when ‘CHAR’ then
CHAR(39) + ‘ ‘ + CHAR(39)
when ‘FLOAT’ then
‘0′
when ‘INT’ then
‘0′
when ‘REAL’ then
‘0′
when ‘DATETIME’ then
‘NULL’
when ‘BIT’ then
‘0′
else
‘ NÃO DEFINIDO ‘
end + ‘,                   ‘,20)
+ ‘ –  ‘ + T.COLUMN_NAME + ‘  ‘+t.DATA_TYPE +CASE t.DATA_TYPE WHEN   ‘CHAR’ THEN ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH  AS VARCHAR)+’)’
WHEN   ‘VARCHAR’ THEN ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH  AS VARCHAR)+’)’
ELSE  ‘ ‘
END
Coluna
from INFORMATION_SCHEMA.COLUMNS t
where t.TABLE_NAME = upper(‘[TABELA]‘)
order by t.ordinal_position
– ABRE CAMPOS COM VALOR DEFAULT PARA UPDATE
select T.COLUMN_NAME
Coluna, ‘ = ‘ +
LEFT(case T.DATA_TYPE
when ‘VARCHAR’ then
CHAR(39) + ‘ ‘ + CHAR(39)
when ‘CHAR’ then
CHAR(39) + ‘ ‘ + CHAR(39)
when ‘FLOAT’ then
‘0′
when ‘INT’ then
‘0′
when ‘REAL’ then
‘0′
when ‘DATETIME’ then
‘NULL’
when ‘BIT’ then
‘0′
else
‘ NÃO DEFINIDO ‘
end + ‘,                   ‘,20)
+ ‘ –  ‘ + T.COLUMN_NAME + ‘  ‘+t.DATA_TYPE +CASE t.DATA_TYPE WHEN   ‘CHAR’ THEN ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH  AS VARCHAR)+’)’
WHEN   ‘VARCHAR’ THEN ‘(‘+CAST(CHARACTER_MAXIMUM_LENGTH  AS VARCHAR)+’)’
ELSE  ‘ ‘
END
Valor_Type
from INFORMATION_SCHEMA.COLUMNS t
where t.TABLE_NAME = upper(‘[TABELA]‘)
order by t.ordinal_position

-- Autor: Djeison Mickael --