Questions tagged [scripting]
For questions about generating or using language scripts, for example scripting a database object definition.
354 questions
28
votes
5
answers
50k
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 ...
- 423
27
votes
4
answers
171k
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,531
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,420
19
votes
1
answer
10k
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. Therefore it should be added to the table as NOT NULL. Unlike previous questions that explain how ...
- 741
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,555
15
votes
1
answer
2k
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 ...
- 1,320
14
votes
4
answers
15k
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 ...
- 341
14
votes
4
answers
29k
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 ...
- 325
13
votes
1
answer
6k
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 ...
- 69.5k
13
votes
4
answers
11k
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
12
votes
1
answer
5k
views
Why does SQL Server Management 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 ...
- 223
11
votes
6
answers
10k
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
11
votes
7
answers
15k
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 ...
- 16.6k
11
votes
4
answers
86k
views
script to show all the permissions for a table
inside my database I have a procedure called spGen2_tblIE_SchedProcess_Update.
when I run this script
select * from sys.objects
where name = 'spGen2_tblIE_SchedProcess_Update'
Inside the same ...
- 16.6k
10
votes
2
answers
29k
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 ...
- 2,907
10
votes
2
answers
9k
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:
...
- 223
10
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 ...
- 103
10
votes
1
answer
23k
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,763
9
votes
1
answer
9k
views
PostgreSQL - How to list all foreign key link to Primary key of a table
I have a table res_users with primary key id
I would like to list all the foreign key link to id from all the tables in my database.
- 193
9
votes
1
answer
21k
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
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
9
votes
2
answers
18k
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 ...
- 658
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,323
8
votes
1
answer
6k
views
postgres create limited user that can select tables and columns of those tables
I have an app that automates the making of a PostgreSQL database, and also automates the creation of a database user. This database user is given limited access for security purposes, as it will be ...
- 237
8
votes
2
answers
2k
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 ...
- 70.5k
7
votes
3
answers
18k
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 ...
- 1,414
7
votes
2
answers
14k
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 ...
- 2,053
7
votes
1
answer
22k
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
7
votes
2
answers
4k
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 ...
- 2,650
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,360
6
votes
5
answers
34k
views
What does "psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)" actually mean?
I have a question regarding following output:
[nikola@192 ~] $ sudo su - postgres
[postgres@192 ~] $ psql dvdrental postgres
psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (...
- 272
6
votes
2
answers
10k
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 ...
- 161
6
votes
1
answer
3k
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' ...
- 1,353
6
votes
2
answers
14k
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, ...
- 213
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]::...
- 12.3k
6
votes
2
answers
473
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,762
6
votes
1
answer
18k
views
use variable to determine the name of a column in the select in Postgres
I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it.
The old table is like this:
Old table (OldTable)
name
col_a
col_b
...
- 173
6
votes
1
answer
5k
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 ...
- 165
6
votes
1
answer
16k
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 ...
- 163
6
votes
2
answers
82k
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
4
answers
16k
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 ...
- 1,505
5
votes
1
answer
4k
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_=...
- 16.6k
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,906
5
votes
1
answer
5k
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 ...
- 327
5
votes
1
answer
2k
views
Passwordless mysqldump via shell script in /etc/cron.daily
I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place.
The problem:
I ...
- 223
5
votes
3
answers
659
views
How to minimize\plan ahead the effect of running sp_delete_backuphistory? Measure the gains too!
while running the following query:
-- DATEADD (datepart , number , date )
DECLARE @dt DATETIME
SELECT @dt = DATEADD(month,-6,getdate())
select @dt
EXEC msdb.dbo.sp_delete_backuphistory @...
- 16.6k
5
votes
2
answers
1k
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
...
- 869
5
votes
1
answer
786
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 ...
- 53
5
votes
2
answers
5k
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 ...
5
votes
2
answers
2k
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 ...
