Questions tagged [scripting]

For questions about generating or using language scripts, for example scripting a database object definition.

Filter by
Sorted by
Tagged with
19
votes
5answers
27k views

SQL Server 2012: Generate Scripts from command line

I am running SQL Server 2012. The SQL Server Management Studio has the option to right click on a database then select Tasks and Generate Scripts. Is there a way to automate that via command line ...
19
votes
3answers
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 ...
18
votes
3answers
124k 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 ...
17
votes
6answers
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 ...
15
votes
1answer
1k views

User-shared queries: Dynamic SQL vs. SQLCMD

I have to refactor and document a number of foo.sql queries which will be shared by a team of DB tech support (for customer configurations and things like that). There are types of tickets that come ...
14
votes
3answers
25k 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 ...
12
votes
4answers
9k 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 ...
11
votes
1answer
1k views

Does SQL Server / T-SQL support line-continuation to break up long strings?

I sometimes have a SQL script that has one or more super-long (sometimes even stupid-long) strings. Typically these are VARBINARY literals / constants that represent files / Assemblies, but ...
10
votes
2answers
27k views

How to script all permissions on a schema

SQL management studio allows to create scripts for all db objects, however I so far couldn't find a way to correctly script a schema or user. The permissions of a user on a schema are not included in ...
10
votes
5answers
6k 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. ...
9
votes
1answer
2k 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 ...
9
votes
2answers
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. ...
9
votes
1answer
16k 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:\...
8
votes
1answer
2k views

Why does SQL Server Mangement Studio not script Indexes by default?

Recently I realized by default, scripting the database does not including indexes, and I have to change the option every time. Could anyone please explain the reason? Is there any insight about this ...
8
votes
2answers
1k views

Scripting an application role with a hashed password

I need to script an application role with a hashed password, so I can copy it from one database to another. Consider the following example code, which uses an Application Role to provide elevated ...
8
votes
2answers
3k 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 ...
7
votes
3answers
9k views

Restore All Databases Script

I am migrating SQL Server DBs to a new instance. I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog. Does anyone know of a sample ...
7
votes
1answer
4k views

Variable inside another variable in SQLCMD mode

Trying to tidy up my SQL script with SQLCMD mode and I ran into an issue: :setvar db_suffix "some_suffix" :setvar some_db "some_db_$(db_suffix)" print 'some_db: $(some_db)' The output of that is: ...
7
votes
2answers
10k views

How to script permissions for all logins/users/tables/schemas

Over the years, I have used countless scripts and applications to generate all permissions for SQL Server databases. I have used both paid and free tools. I feel that not one of them seems to be ...
7
votes
2answers
1k views

How to back up an entire database to text files

If it’s stupid and it works, it’s still stupid. The hullabaloo about GDPR got me thinking - if you had to go into your backups and modify/delete data, how would you do it? Cracking open a .bak file ...
6
votes
4answers
6k 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 ...
6
votes
1answer
2k views

SQL Server - Adding non-nullable column to existing table - SSDT Publishing

Due to business logic, we need a new column in a table that is critical to ensure is always populated. Updating existing references that insert/update is difficult but doable, but ensuring future code/...
6
votes
2answers
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]::...
6
votes
2answers
415 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
votes
2answers
69k 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....
6
votes
1answer
11k views

Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'

For SQL Server 2012 and above, as What's New in SQL Server Installation states: BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed ...
5
votes
2answers
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, ...
5
votes
1answer
2k views

Using X at the end of a SELECT in SQL Server Script?

I'm relatively new to T-SQL and looking through my predecessors scripts, I found a line I can't quite parse. It's a hard thing to look up on my own. Hoping for advice. This line occurs inside a ...
5
votes
1answer
11k 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 ...
5
votes
1answer
2k views

Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016?

Migrating our database from SQL Server 2012 to 2016, we can't recreate an SQL Agent job that involves an ActiveX Scripting step written in VBScript. The error message suggests that 'ACTIVESCRIPTING' ...
5
votes
2answers
447 views

SSMS constraint script: Purpose of second alter table statement?

SSMS scripts Foreign Key constraints as two statements: ALTER TABLE {table} WITH CHECK ADD CONSTRAINT {constraintname} {constraint spec} GO ALTER TABLE {table} CHECK CONSTRAINT {constraintname} GO ...
5
votes
1answer
273 views

Getting column information from the MySQL command line

I am trying to get all the columns from MySQL using bash scripting. I want them to be stored in an array. If I write it this way: mysql -uroot -pPassword1 "select column_name from ...
5
votes
1answer
1k views

Sqlcmd: Invalid argument - when trying to pass a variable to sqlcmd

I'm trying to use a DOS command prompt on Server 2012R2 to run a batch with the following in it. I want to run a SQL command (sqlcmd) and return the results to the command window. This is what I'm ...
5
votes
1answer
808 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 ...
4
votes
4answers
13k views

Get object script in SQL Server via T-SQL?

I need to get the creation script of an object in a SQL Server database. I need the exact same script that was used to create it. Like when you use SSMS to alter a view you already defined, and then ...
4
votes
1answer
993 views

What are the rules for using COLLATE in a query?

I am putting a script together, that gives me the permissions on database objects. SELECT permission_order=710 ,permission_type = 'Object Level Permissions' ,db = db_name(), login_=...
4
votes
2answers
6k views

How to set password for PostgreSQL database with a script?

In a bash script I'm writing, I need to create a database and set a password that is generated automatically by my script. The problem is I can't find a way to set up the password without user ...
4
votes
1answer
394 views

Breaking up insert statements with GO [closed]

I'm trying to build off of this answer How do I run a large script with many inserts without running out of memory? and break up a query into smaller ones with BEGIN TRANSACTION and then GO's ...
4
votes
2answers
3k 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 ...
4
votes
1answer
198 views

Scripting a Maintenance Plan in SQL Server Express

How would I run each aspect of Ola Halengren's sql maintenance plan on SQL Express? It's my understanding that each job should be run in a specific order. I'm sure that a .bat or cmd file can do ...
4
votes
5answers
4k views

How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?

object_definition and sp_helptext both return the source code of a stored procedure - but how can I use object_definition and get the source code including the line breaks as the sp_helptext currently ...
4
votes
2answers
8k views

Scripting SQLite with dot commands

Is it possible to write scripts that contain SQLite dot commands ( vis. .read file.sql; .separator ,; .import file.csv; )? I'm building and repeatedly rebuilding an SQLite database and need to type ...
4
votes
2answers
8k views

How do I pass multiple statements into sqlcmd?

Wanting to automate some tasks instead of firing up SSMS for everything, I'm trying to write a batch file to run these statements, which work perfectly when I paste them into the sqlcmd prompt. They ...
4
votes
2answers
4k views

MySQL Script to automate creation of audit (history) tables and triggers?

For regulatory and fraud requirements, we need to record every change to most of the tables in the DB. The way we did this very successfully with a previous project is to have a copy of each table, ...
4
votes
1answer
15k 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. ...
4
votes
1answer
5k 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) ...
4
votes
1answer
116 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
4
votes
1answer
4k views

Does SQL Server 2008 have an equivalent to Oracle's “table%rowtype” syntax or (Data Type)?

In Oracle PL/SQL scripts, you can declare a variables using the syntax tablename%rowtype for its type. This will make that variable have the same "type" as a row of that table (i.e. a it will be a ...
4
votes
4answers
5k 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 ...
4
votes
1answer
5k views

Script all tables in a database

In SQL Server 2008 - is there any script available which will list all tables (incl column names, data type, length) for the entire database - with one script.