You may optionally enter a comparison operator (<, <=, >, >=, <> or =) at the beginning of each of your search values to specify how the comparison should be done.
SELECT
*
FROM
(
SELECT
TABLES_P.TABLE_SCHEMA AS [SCHEMA_P]
, TABLES_P.TABLE_NAME AS [OBJECTNAME_P]
, TABLES_P.TABLE_TYPE AS [OBJECTTYPE_P]
FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.TABLES AS TABLES_P
UNION ALL
SELECT
ROUTINES_P.ROUTINE_SCHEMA AS [SCHEMA_P]
, ROUTINES_P.ROUTINE_NAME AS [OBJECTNAME_P]
, ROUTINES_P.ROUTINE_TYPE AS [OBJECTTYPE_P]
FROM <DATABASE_PRODUCTION>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_P
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER TABLE' AS [OBJECTTYPE_P]
FROM
<DATABASE_PRODUCTION>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_PRODUCTION>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER VIEW' AS [OBJECTTYPE_P]
FROM
<DATABASE_PRODUCTION>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_PRODUCTION>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id
) ss_P
FULL OUTER JOIN
(
SELECT
TABLES_E.TABLE_SCHEMA AS [SCHEMA_E]
, TABLES_E.TABLE_NAME AS [OBJECTNAME_E]
, TABLES_E.TABLE_TYPE AS [OBJECTTYPE_E]
FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.TABLES AS TABLES_E
UNION ALL
SELECT
ROUTINES_E.ROUTINE_SCHEMA AS [SCHEMA_E]
, ROUTINES_E.ROUTINE_NAME AS [OBJECTNAME_E]
, ROUTINES_E.ROUTINE_TYPE AS [OBJECTTYPE_E]
FROM <DATABASE_DEVELOPMENT>.INFORMATION_SCHEMA.ROUTINES AS ROUTINES_E
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER TABLE' AS [OBJECTTYPE_P]
FROM
<DATABASE_DEVELOPMENT>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_DEVELOPMENT>.sys.tables AS TAB ON TRIG.parent_id = TAB.object_id
UNION ALL
SELECT
'' AS [SCHEMA_P]
, TRIG.name AS [OBJECTNAME_P]
, 'TRIGGER VIEW' AS [OBJECTTYPE_P]
FROM
<DATABASE_DEVELOPMENT>.sys.triggers AS TRIG
INNER JOIN
<DATABASE_DEVELOPMENT>.sys.views AS TAB ON TRIG.parent_id = TAB.object_id
) ss_E ON ss_P.[SCHEMA_P]=ss_E.[SCHEMA_E] AND ss_P.OBJECTNAME_P=ss_E.OBJECTNAME_E AND ss_P.OBJECTTYPE_P=ss_E.OBJECTTYPE_E
154
PL/pgSQL
Datenbank
Erstellen und Befüllen Tabelle aus Query
DROP TABLE myTable;
CREATE TABLE myTable AS SELECT * FROM mySecondTable;
151
T-SQL
Datenbank
Automatisch hochzählendes Feld
CREATE TABLE myTable
(
id INT IDENTIY(1,1),
field1 varchar(100)
)
152
PL/pgSQL
Datenbank
Automatisch hochzählendes Feld
CREATE TABLE myTable
(
id SERIAL,
field1 varchar(100)
)
147
PL/pgSQL
Datum, Zeit
Jahr aus Datum extrahieren
select extract(year from now())
149
Perl
Datum, Zeit
Jahr aus Datum extrahieren
use POSIX qw/EXIT_SUCCESS EXIT_FAILURE/;
# Datum Vorbereitung:
my $DateTime = POSIX::strftime('%c', localtime(time));
my ($Date, $Time) = split(/\s+/, $DateTime);
my ($Day, $Month, $Year) = split(/\./, $Date);
my ($Hour, $Minute, $Second) = split(/:/, $Time );
my $Datum = "$Year"."$Month"."$Day";
#oder
# my $Datum = "$Day"."$Month"."$Year";
my $Uhrzeit = "$Hour".":"."$Minute".":"."$Second";
211
SQLite
Datum, Zeit
Jahr aus Datum extrahieren
SELECT strftime('%Y', '2014-08-25') AS Year;
230
MySQL
Datum, Zeit
Jahr aus Datum extrahieren
SELECT extract(year from now()) AS Year;
SELECT extract(year from '2014-01-01') AS Year;
-- 2014
146
PL/pgSQL
Datenbank
Tabelle kopieren
CREATE TABLE new_table AS SELECT * FROM old_table
-- this action can't be done cross databases
