Questions tagged [scripting]
For questions about generating or using language scripts, for example scripting a database object definition.
41
questions
7
votes
3
answers
16k
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,384
27
votes
5
answers
43k
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 ...
- 413
1
vote
2
answers
4k
views
script all triggers in a database
I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.
Every time I need to reinitialise those database I need to script ...
- 15.4k
11
votes
4
answers
67k
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 ...
- 15.4k
2
votes
1
answer
1k
views
How to script out the grant view on login permission?
I create the following login without any permissions.
USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe')
CREATE LOGIN [Radhe] WITH PASSWORD=N'...
- 15.4k
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
2
votes
1
answer
3k
views
Import data from one Database to another script
As part of a data migration I would like to script a data transfer from the existing to a new database.
I used to do this with DTS on older versions of MS SQL. I am currently using SQL 2012.
Note the ...
- 131
0
votes
1
answer
4k
views
SQL Mail Failing To Send
SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail ...
- 47
18
votes
1
answer
8k
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 ...
- 711
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
3
votes
1
answer
10k
views
How to generate create table script without primary and foreign key in Oracle
I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.
SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','...
- 433
3
votes
3
answers
5k
views
Use sp_Blitz to gather information on all servers in a centralized server/database
I am using sp_Blitz, and want to use it to execute the checks on all of my SQL Servers and have it write the results to a single table.
Do I need to create linked servers on the central instance to ...
- 539
2
votes
1
answer
169
views
Trace flag is running, but not message when deadlock happens
Trace flags are used to temporarily set specific server
characteristics or to switch off a particular behavior
More information on the trace flags are here
DBCC TRACESTATUS
this above, is the ...
- 15.4k
0
votes
1
answer
3k
views
List of role permissions including other roles - how can I find out the roles that belong to other roles?
I can see the permissions of a role in a database using the scripts in
List all permissions for a given role?
However, It does not show me when the role belongs to a another role.
For instance, on ...
- 15.4k
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
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
11
votes
1
answer
4k
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 ...
- 213
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
6
votes
2
answers
13k
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, ...
- 177
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 ...
5
votes
2
answers
1k
views
how to remove all permissions in a database?
every time I need to either restore a database or reinitialize a replication publication I first (on the restored to be or subscription database) I script all the permissions, triggers, indexes, ...
- 15.4k
4
votes
1
answer
2k
views
Script out XMLA for all databases
I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have.
How do I auto-generate ...
- 325
4
votes
2
answers
4k
views
how to script out the user defined table types?
I can Get name and definition of all table types
using either of the following scripts:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL
DROP TABLE #...
- 15.4k
4
votes
3
answers
4k
views
parse rs.status() output to display one secondary node for mongodump destination
I wish to run mongodump to secondary (not primary). So I need as output hostname and port of one secondary.
# ./mongo -udbadm admin --port 27100 -p --quiet --eval "printjson(rs.status().members.map(...
- 2,308
4
votes
4
answers
10k
views
Need to get identify all tables and columns in a SQL Query
I have a massive task to go through hundreds of SQL scripts and identify the Table and Columns in each query as we are updating our system and some of the columns and tables may be renamed or ...
- 83
3
votes
1
answer
6k
views
ERROR: "sql " is not a known variable
This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running.
I'm invoking the cobertura.sql file using ...
- 933
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
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
2
votes
0
answers
935
views
How do I create an automated SQL Server restore script for multiple backups? [duplicate]
I basically have 6 daily SQL Server backups (.bak) that need a restore everyday. I want to automate this process by having the SQL Agent run the script daily. I have the following restore script ...
- 21
2
votes
0
answers
854
views
SSIS Script Component errors sporadically with the same code
I have a SSIS Data Flow Task that takes CSV output (from a flat file connection) and passes that to a Script Component. When I run the task I get the following error:
Script Component has encountered ...
- 2,276
1
vote
0
answers
588
views
How to simulate slow queries on MongoDB
I need to write a shell script that finds and kills long running mongodb queries, but I don't have access to the database that runs slow queries, how to I simulate running slow queries on my local ...
- 69
1
vote
1
answer
617
views
Numbering rows consecutively for a number of tables
I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,...
- 848
1
vote
1
answer
248
views
Generate SQL scripts in powershell
I want to be able to generate simple repetitive scripts in SQL using Powershell as the scripting language.
For example, If I want to generate a SQL GRANT command for every table in a database, and ...
- 4,153
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
1
vote
1
answer
1k
views
MySQL backing up MyISAM tables by copying the physical files - and automating the process
I have 2 databases with MyISAM tables running on a Windows 2008 Server. These databases are about 20GB in size with a few tables with millions of rows. My question regards backing them up on a weekly ...
- 13
1
vote
1
answer
3k
views
ONLINE BACKUP is not performed from the CRON Job on LINUX server (manually it works)
I made a script which executes successfully online backup when it is run manually by the instance owner.
Script is the following (two lines originally).
#!/bin/bash
/opt/ibm/db2/V9.7/bin/db2 ...
- 645
1
vote
1
answer
437
views
Include scripts for Indexes on Alter Views
After a Developer altered an Indexed View to add a comment, the clustered index that was created on it was dropped with no warning.
This caused a failure as there are a couple of procedures that have ...
- 1,793
0
votes
3
answers
1k
views
History of actions that occured on a table in SQL Server
I am running a shell script that runs insert, update, delete queries on a table in an SQL Server DB. Is there a way to get a history of all transactions that happened on the table to validate that ...
- 109
0
votes
2
answers
5k
views
Set default value for all columns with specific name within a database
I've got a database that includes an int column project_id on many tables, and I want to script setting the default value to a specific value for all columns with that name in the database. Note that ...
- 181
0
votes
0
answers
73
views
Loading 260GB worth of CSV into MySQL in Linux
I'm bulk loading a few thousand CSVs totaling ~260GB and ~4.5 billion rows to MySQL using mysqlimport calls on each CSV executed within a bash script. The MySQL installation and the data itself are ...
- 181
0
votes
2
answers
2k
views
How to script all the service broker elements for each database it is on, in the current server?
I have service broker running on different servers.
Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there.
For the next time I need to ...
- 15.4k