Generating Insert statements for MS Sql Server table data

The file below contains an adjusted script for generating insert statements for sql server table data. It was originally written by Narayana Vyas Kondreddi. You can find example usage inside the script.

sp_generate_inserts

Note that this procedure's name has the devilish sp_ prefix, which means that it better be created in the master database.  This way, it will be available to all databases in an sql server instance.  Otherwise you'd have to create it on each database on your server.

Oracle SQL To find Unindexed Foreign Keys

Oracle SQL To find Unindexed Foreign Keys You can easily extend this to produce sql that creates the foreign key indexes.

column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '--', 'ok' ) Status, 
    a.table_name, a.columns, b.columns
from 
( select substr(a.table_name,1,30) table_name, 
   substr(a.constraint_name,1,30) constraint_name, 
      max(decode(position, 1,     substr(column_name,1,30),NULL)) || 
      max(decode(position, 2,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position, 3,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position, 4,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position, 5,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position, 6,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position, 7,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position, 8,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position, 9,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position,10,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position,11,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position,12,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position,13,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position,14,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position,15,', '||substr(column_name,1,30),NULL)) || 
      max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_cons_columns a, user_constraints b
   where a.constraint_name = b.constraint_name
     and b.constraint_type = 'R'
   group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a, 
( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name, 
      max(decode(column_position, 1,     substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) || 
      max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
    from user_ind_columns 
   group by substr(table_name,1,30), substr(index_name,1,30) ) b
where a.table_name = b.table_name (+)
  and b.columns (+) like a.columns || '%'
/

Script to generate all Primary and Foreign Key Constraints In an Oracle database

STEPS TO EXECUTE:

1. SAVE THE ATTACHED FILE TO C:\
2. LOGIN TO SQLPLUS
3. TYPE: @C:\getConstraints.sql
4. WHEN THIS FINISHES, ALL DDL WILL BE IN FILE c:\constraintsDDL.sql

---------------- getConstraints.sql -------------------------------
prompt Exporting User Constraints....
set feedback off
set heading off
set termout off
set linesize 3000
set long 90000
set trimspool on
column CODE format a300
set verify off
prompt set define off
spool c:\constraintsDDL.sql
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
select trim(DBMS_METADATA.GET_DDL('CONSTRAINT',t.constraint_name))||';' as CODE
from user_constraints t where t.constraint_type='P';
select trim(DBMS_METADATA.GET_DDL('REF_CONSTRAINT',t.constraint_name))||';' as CODE
from user_constraints t where t.constraint_type='R';
spool off
prompt set define on
set feedback on
set heading on
set termout on
set linesize 100
-----------------------------------------------------

An MVC Checklist (Java)

Model-View-Controller (MVC2) and Model-View-Controller 2 (MVC2) are the de-facto patterns to use when developing Java and .NET web applications.

Here is a checklist to follow in order to apply the MVC pattern in Java:

1. Each of your JSP page (the View) has a corresponding servlet (the Controller).
2. Your database objects are mapped to "Model Objects", usually POJOs that mirror the database structure to Java Objects. (the Model).
3. From your web application pages, you have no direct html links to your JSP pages. Instead, your links point to the corresponding servlet of each JSP page.
4. Each JSP page posts back to its Controller servlet. In other words, the <form&ht; tag on your JSP page has the action attribute set to the Controller servlet url.
   Example: <form method="POST" action="/myServlet">
5. Each Controller Servlet handles/checks for "actions" and after processing, forwards or redirects to the JSP View Page. Here are some standard Controller Servlet actions:
   • "edit": where the Controller Servlet calls the Model which loads a record from a database based on same criteria, and then forwards to the JSP View page. The record is shown to the user available for editing.
   • "save": where the Controller Servlet loads data from the Http Request to the correspondind Model Object, calls a save routine and then forwards to a JSP View page.
   • "create": where the Controller Servlet calls the correspondind Model Object's create method, and then forwards to a JSP View page. This is where our uses can create new records.
   • "delete": where the Controller Servlet calls the corresponding Model Object's delete method, and then forwards to a JSP View page. This is where our uses delete records.

Here is the MVC diagram: (source: Wikimedia Commons)

The solid line represents a direct association, the dashed an indirect association (via an observer for example).

Visual Basic Function to get Eastern Orthodox Easter for a Year

From http://www.smart.net/~mmontes/ortheast.html#ALG

Function getEasterDate(year As Integer) As Date
    
    Dim GoldenNum As Integer
    Dim daysToPaschalFullMoon As Integer
    Dim weekdayOfPaschalFullMoon As Integer
    Dim numDaysFrom21ToPaschalFullMoon As Integer
    Dim EasterMonth As Integer
    Dim EasterDay As Integer
    
    GoldenNum = year Mod 19
    daysToPaschalFullMoon = (19 * GoldenNum + 15) Mod 30
    weekdayOfPaschalFullMoon = (year + year / 4 + daysToPaschalFullMoon) Mod 7
    numDaysFrom21ToPaschalFullMoon = daysToPaschalFullMoon - weekdayOfPaschalFullMoon
    EasterMonth = 3 + (numDaysFrom21ToPaschalFullMoon + 40) / 44
    EasterDay = numDaysFrom21ToPaschalFullMoon + 28 - 31 * (EasterMonth / 4) + 13

    getEasterDate = DateSerial(year, EasterMonth, EasterDay)
    
End Function

PL/SQL proc to add a column to a table if it does not exist

create or replace function ddl_column_exists(p_table     in varchar2,
                                             p_fieldname in varchar2) return number is
  
  result number;

begin
  select count(*)
    into result
    from user_tab_columns c
   where lower(c.COLUMN_NAME) = lower(p_fieldname)
     and lower(c.TABLE_NAME) = lower(p_table);

  if result >0 then
    result := 1;
  else
    result := 0;
  end if;

  return(result);
end;
/
CREATE OR REPLACE PROCEDURE "DDL_USP_ADDCOL" (p_tblName varchar2,
                                              p_fldName varchar2,
                                              p_dtype   varchar2) iS
    v_ret number(2) := 0;
    v_sql varchar2(2000);
begin

    select count(*)
      into v_ret
      from user_tab_columns c
     where lower(c.COLUMN_NAME) = lower(p_fldName)
       and lower(c.TABLE_NAME) = lower(p_tblName);

    if v_ret = 0 then

        --print 'adding field'
        v_sql := 'ALTER TABLE ' || p_tblName || ' add ' || p_fldName || ' ' || p_dtype;
        execute immediate v_sql;

    end if;
end;

Script to export all pl/code in an oracle database

---------------- getcode.sql -------------------------------
prompt Exporting User code....
set feedback off
set heading off
set termout off
set linesize 3000
set trimspool on
set verify off
prompt set define off
spool c:\policecode.sql
select text from (
select a.name, a.type, a.line, decode(line,1,'create or replace ', '' ) || text  as text
  from user_source a
union select a.name, a.type, 9000000000 as line, '/' from user_source a
order by name,type, line);
spool off
prompt set define on
set feedback on
set heading on
set termout on
set linesize 100
-----------------------------------------------------