#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    3
    Rep Power
    0

    How to see only particular table details by using perlscript.


    Hi,

    I written the below perl sciprt this will give particular employee
    table details.But i want to see each time by specifying different table names as inpuT tparametes.Ex:next time i want to see
    only customer table details only.So HOW I CAN GET THIS USING PERL SCRIPT.

    Please check the attached script for particular table(employee)
    DB:SQLSERVER
    script:

    use DBI;



    # DBD::ODBC



    my $dsn = 'DBI:ODBC:Driver={SQL Server}';

    my $host = '100.10.100,1433';(---sampleip)

    my $database = 'test';(---sampledb name)

    my $db_Metadata = 'test';(---for sample)

    my $user = 'xx';

    my $auth = 'xxxx';

    my %hash = ();




    # Connect via DBD::ODBC by specifying the DSN dynamically.

    my $dbh1 = DBI->connect("$dsn;Server=$host;Database=$database",

    $user,

    $auth,

    { RaiseError => 1, AutoCommit => 1}

    ) || die "Database connection not made: $DBI::errstr";


    my $tabsql ="select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,NUMERIC_PRECISION,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS where table_name ='employee'\n";
    my $sth = $dbh1->prepare( $tabsql );


    my $sth = $dbh1->prepare( $tabsql );



    #Execute the statement

    $sth->execute();


    my($TABLE_SCHEMA,$TABLE_NAME,$COLUMN_NAME,$DATA_TYPE,$NUMERIC_PRECISION,$CHARACTER_MAXIMUM_LENGTH,$I S_NULLABLE);

    # Bind the results to the local variables

    $sth->bind_columns(undef,\$TABLE_SCHEMA,\$TABLE_NAME,\$COLUMN_NAME,\$DATA_TYPE,\$NUMERIC_PRECISION,\$CHAR ACTER_MAXIMUM_LENGTH,\$IS_NULLABLE );



    #Retrieve values from the result set

    while( $sth->fetch() ) {

    print "$TABLE_SCHEMA,$TABLE_NAME,$COLUMN_NAME,$DATA_TYPE,$NUMERIC_PRECISION,$CHARACTER_MAXIMUM_LENGTH,$IS_ NULLABLE\n";

    }

    $sth->finish();

    $dbh1->disconnect();
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,940
    Rep Power
    1225
    Use Placeholders.

    Code:
    my $table = shift @ARGV;  # in this example, tablename is passed to the script
    
    # lets add some readability to the sql statement
    my $tabsql = "select TABLE_SCHEMA,
                         TABLE_NAME,
                         COLUMN_NAME,
                         DATA_TYPE,
                         NUMERIC_PRECISION,
                         CHARACTER_MAXIMUM_LENGTH,
                         IS_NULLABLE
                  from INFORMATION_SCHEMA.COLUMNS
                  where table_name = ?";
    
    my $sth = $dbh1->prepare( $tabsql );
    
    #Execute the statement
    $sth->execute($table);

    Comments on this post

    • keath agrees

IMN logo majestic logo threadwatch logo seochat tools logo