Questions tagged [scripting]
For questions about generating or using language scripts, for example scripting a database object definition.
344
questions
1
vote
2
answers
2k
views
How can I run a T-SQL Script using SQL Credentials?
I need to develop a T-SQL insert script for use on a remote SQL 2005 Express instance but I would like the script to include the necessary SQL credentials so all the user has to do is execute the ...
- 161
5
votes
1
answer
1k
views
How to schedule PostgreSQL replication?
I was reading postgresql replications solution but, even I just starting understaning how it works, another doubt has arrisen.
I'll be using postgres internal replication solution but as far as I ...
1
vote
1
answer
2k
views
Drop database from remote that has connected users [duplicate]
I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent ...
1
vote
1
answer
3k
views
Generate script for SQL Broker objects
I'm using SQL Server Management Studio to generate scripts for all the objects in an existing database:
but the objects related to SQL Broker (messages, queues and services) are not generated.
I've ...
- 113
2
votes
4
answers
16k
views
SQL Server - backup / restore schema only
We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - ...
- 131
8
votes
1
answer
18k
views
SQL Server - Must be first statement in query batch - what and why?
I cannot find a list of type of objects that while being created the command must be the first statement in query batch. So far I ve found that function/stored_procedure/view must be the first.
Does ...
9
votes
2
answers
4k
views
Variable substitution in psql when using \copy
I'm using the psql command \copy and I would like to pass a variable to it from the shell (for table name) like I've done when scripting queries. I've read in the documentation for psql that:
The ...
2
votes
1
answer
113
views
How to create an object and then alter it
I'm using sql server 2012:
I would like to script out creation of an object(if it doesn't exist first), and then alter it.
I'm trying to avoid the usual drop/create as it would impact stats. It ...
- 923
5
votes
2
answers
1k
views
Perl vs Ksh for Unix Database Administration
I realize this is a somewhat more subjective question, but I am looking to the community for guidance.
Our company is fairly new to having DBAs. We used to use DB2 for i on the IBM's iSeries servers, ...
- 4,896
0
votes
2
answers
3k
views
Reloading .sql file from java into PostgreSQL - Windows
I am trying to reload a .sql script file into PostgreSQL from my sample Java application.
Script is successfully executed from console as well as from pgAdmin tool. But from Java, it hangs...
This ...
- 541
3
votes
2
answers
396
views
Defrag a HEAP by creating clustered index and immediately dropping it
I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it. (It's someone else's app and I don't want to make any ...
2
votes
2
answers
6k
views
SQL Server database generate script how to avoid adding PRINT statements
I need to create Insert scripts from the content of a table. To do that, I used the database tasks -> generate scripts.
The table is quite big and in the generated script, it adds PRINT command after ...
- 3,674
6
votes
2
answers
79k
views
How to store single result in variable and reuse it in a query (Oracle)?
I have a query like
SELECT id FROM xyz WHERE ...;
which has a more or less complex WHERE clause and returns exactly one row with one column (ID). I need this ID for several later queries in a script....
- 167
5
votes
2
answers
4k
views
How to compare two SQL Servers instance level objects for a migration?
I'm currently working on a SQL Server migration (from a SQL Server 2005 to a 2008 R2 server, on a new physical server).
The databases themselves will be transferred using a database backup and ...
3
votes
1
answer
2k
views
SSIS Script to split string into columns
I have a dataset (log file) with a number of columns; one of them is "Other-Data" below (unordered string) and need to parse the string to create the derived columns according the u value (U1, U2, U3, ...
- 31
2
votes
1
answer
2k
views
Rollback generate scripts
During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new ...
- 163
1
vote
1
answer
192
views
Regularly import tab-seperated log file to MySQL
I'm looking to keep arpwatch entries in a MySQL database to crossreference with other information I'm storing based on mac addresses. I've manually imported the arpwatch database into my mysql ...
- 13
0
votes
2
answers
334
views
Auto Script DB Objects for SVN
My Boss requires that we keep up to date scripts of all database objects in svn. This results in constantly trying to find the object in the current script and copy and pasting the changes.
Is there ...
- 2,099
3
votes
3
answers
1k
views
Save Results of DBCC ShrinkFile
When we execute the following command:
DBCC SHRINKFILE('MyDB_log', 1)
We get the following results in SSMS:
DBID | Field | CurrentSize | MinimumSize | UsedPages | Estimated Pages
-----|-------|-----...
- 1,089
6
votes
4
answers
7k
views
Script Contents of SQL Server Database From a Batch File
I want to be able to script the schema of a given database into a .sql file from the Windows command prompt. Basically, I want to execute the "Generate Scripts" feature of Management Studio ...
- 7,240
9
votes
1
answer
3k
views
Executing TSQL script generated from SSMS in SQLCMD. Failing on quotes
I am attempting to deploy a database to a machine by executing a script generated by the "Generate Scripts" tool in SQL Server Management Studio.
Here is the command I've issued:
sqlcmd -S ...
- 93
23
votes
4
answers
143k
views
Easy way to check connectivity to SQL Server from client
For troubleshooting purposes, I would like to be able to check if a client can connect to a SQL Server instance, independent of the application that possibly can't connect to the SQL Server.
Is there ...
- 2,441
1
vote
1
answer
130
views
Is there a way to create a re-usable base table definition in InfoSphere Data Architect?
Our company has chose to use IBM's InfoSphere Data Architect (IDA) for data architecting regarding our data warehouse and data marts.
The data architects originally designed several "re-usable" ...
- 4,896
2
votes
2
answers
3k
views
What is running right now?
I know I can use "show processlist" to see what is running when I'm in the office. How can I see what is running when I'm not there.
Something is running on my server at 3am every morning that is ...
- 87
7
votes
1
answer
21k
views
Drop multiple databases in one script
I am trying to figure out how to drop multiple databases on PostgreSQL in one sql script. I preferably need it to work both when executed in pgAdmin console, and when used in psql command line tool. ...
- 173
4
votes
1
answer
6k
views
How, if possible, can an .sql file be executed against an oracle database from a bash script
Is it possible to execute an sql file (a text file containing several SQL statements) against an oracle database from a bash shell command line (e.g. no graphical GUI and no interactive clients)
...
- 157
10
votes
1
answer
22k
views
SQL*Plus, @, and relative paths
Somehow, it seems that SQL*Plus (at least on Windows) is unable to locate a script with a relative path when called with @@ and when the path starts with a single or double dot.
For example, under x:\...
- 3,733
11
votes
6
answers
9k
views
How can I detect broken stored procedures after a schema change?
I've modified a central table in my database, and sp_depends literally returns hundreds of results, and I'm concerned some of those stored-procedures might not compile anymore after my change.
...
- 325
1
vote
2
answers
323
views
PostgreSQL Database Migration
How can I migrate data from on PostgreSQL database to another PostgreSQL database? Is there any technique to migrate data with SQL scripting?
- 113
9
votes
2
answers
4k
views
Is there any collection of PowerShell scripts that help the task of SQL Server maintenance?
I've been studying PowerShell and I've been (slowly) getting it into my day-to-day DBA tasks.
So far I've seen a bunch of disconnected PowerShell scripts for many disparate administration activities.
...
- 2,303
4
votes
3
answers
4k
views
Scrubbing Names via SQL Query/Batch
We have sensitive information (names of people) and we're looking to put into place a script to "scrub" of the names. Has anybody come up with a good algorithm to do this, something other than just ...
- 587
1
vote
1
answer
173
views
Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?
I have this existing table for IP storage:
CREATE TABLE [dbo].[IPAddresses](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IPv4Address] [varchar](15) NULL,
[IPv6Address] [varchar](45) NULL,
...
- 1,667
3
votes
3
answers
28k
views
Exporting table records to an INSERT script
I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required ...
- 1,257
1
vote
3
answers
1k
views
SQL Server 2008 R2 Express Edition - generating database create script
I need to script a database schema out to T-SQL for inclusion source control. The Express version of SSMS doesn't seem to let you script the entire database, just individual tables. Are there any free ...
- 417
14
votes
4
answers
28k
views
Script out Oracle DDL in an automated fashion
Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention.
I know of DBMS_METADATA.get_ddl(), but have found that the ...
- 315
1
vote
2
answers
518
views
ORM-style server-side programming languages (OO replacement for PL/SQL?)
Good evening,
Is there an Object Oriented replacement for PL/SQL, allowing server side procedures to be written [and then called client or server side]?
(for MySQL, PostgreSQL, Oracle or etc.)
- 391
2
votes
1
answer
3k
views
Automated UML2 Class Diagram generation from SQL?
How can I generate a class diagram from an SQL database or script? I'll add the functions and set whether the attributes are public, private or virtual once the initial diagram is generated.
I've ...
- 391
4
votes
4
answers
6k
views
how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio
We are a software engineering firm. We use SQL Server Management Studio (SSMS) to generate the few scripts we need to build and upgrade our databases.
While it does an OK job, it bugs me that it ...
- 225
19
votes
3
answers
2k
views
Alternatives to concatenating strings or going procedural to prevent SQL query code repetition?
Disclaimer: Please bear with me as someone who only uses databases a tiny fraction of his work time. (Most of the time I do C++ programming in my job, but every odd month I need to search/fix/add ...
- 2,410
13
votes
4
answers
10k
views
Dropping a constraint (index) on a column
How can I modify the type on a table that has an index on it? I tried to do an alter column on an empty table to modify the type from date time to varchar(15) and got errors saying that it had ...
- 231
17
votes
6
answers
4k
views
Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project
I currently use the Visual Studio Professional Edition which has the database project as a project template, but some of its features are not available, for example Schema Compare tool. The schema ...
- 1,525
2
votes
2
answers
2k
views
How can can I get object definitions stripped of all comments?
When I want to know if a stored procedure or views references a given name (table, view or column), I need a version of the object definition stripped of all comments.
Is this possible by a SQL ...
- 12k
6
votes
2
answers
2k
views
Can I call SMO from pure T-SQL?
I know how to script a stored procedure using PowerShell and SMO:
[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::...
- 12k
6
votes
2
answers
454
views
How to manage my own scripts
Every administrators, developers have their own collection of scripts (some patterns). Time to time they are dismissed from one job and they are employed to other one. Is there any good, easy used, ...
- 6,652