34 votes

Easy way to check connectivity to SQL Server from client

A simple test method for SQL connectivity is to create an empty text file, with a file extension of "UDL". You can create it in Notepad. And it can have any name. I use "TestSQL.UDL" Save it on ...
DaveB's user avatar
  • 341
21 votes
Accepted

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

I'll share how I have done this in the past. It is designed to solve the specific limitation of pre-deployment scripts that you call out in your second point: Adding the column in a pre-...
Josh Darnell's user avatar
  • 29.2k
15 votes

SQL Server 2012: Generate Scripts from command line

Tara Raj from Microsoft recently announced that the Microsoft SQL team has released a set of command line tools to generate T-SQL scripts that appear to do exactly what you've asked: mssql-scripter ...
BradC's user avatar
  • 9,954
15 votes

Variable inside another variable in SQLCMD mode

Is there a way to have variable interpolation like this? Kinda sorta, in a way. Just not directly. You need to keep in mind a few things: The value that you set a variable to via :setvar is a ...
Solomon Rutzky's user avatar
15 votes
Accepted

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

Thankfully there is support for line-continuation in T-SQL via the \ (backslash) character. Just place that at the end of of a line, right before the newline / carriage-return + line-feed, and the ...
Solomon Rutzky's user avatar
12 votes
Accepted

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

You don't have to change the option every time. If you change the selection in Tools > Options > Object Explorer > Scripting, it should persist. As for why not scripting indexes is the default, I ...
Aaron Bertrand's user avatar
12 votes
Accepted

What are the rules for using COLLATE in a query?

COLLATE operates per predicate or per expression, depending on context. COLLATE is mainly used to control how the string values are being compared or sorted. Hence it is most commonly used in JOIN, ...
Solomon Rutzky's user avatar
10 votes
Accepted

Scripting SQLite with dot commands

I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately. The gist of it? Put your ...
StudentsTea's user avatar
10 votes
Accepted

Breaking up insert statements with GO

PROBLEM NUMERO ONE GO is the default query batch separator. It is a feature of certain client utilities (mainly SQLCMD and SSMS) and is unknown to the SQL Server engine itself. GO needs to be on a ...
Solomon Rutzky's user avatar
9 votes
Accepted

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

Discontinued Database Engine Functionality in SQL Server 2016 indicates ActiveX subsytem is discontinued. Use command line or PowerShell scripts instead.
Scott Hodgin - Retired's user avatar
9 votes
Accepted

Query system specs (cpu, memory, data disks, free space) using T-SQL

Yes, you bet. Let's start with the open source First Responder Kit (disclaimer: I'm one of the script authors) - install sp_Blitz, and then: sp_Blitz @CheckServerInfo = 1 And at the bottom, you get ...
Brent Ozar's user avatar
  • 42.9k
9 votes
Accepted

PostgreSQL - How to list all foreign key link to Primary key of a table

You can query the catalog tables: SELECT la.attrelid::regclass AS referencing_table, la.attname AS referencing_column FROM pg_constraint AS c JOIN pg_index AS i ON i.indexrelid = c....
Laurenz Albe's user avatar
  • 50.2k
9 votes
Accepted

What does "psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)" actually mean?

You have installed a mixture of packets: some from the Fedora distribution, some from the PostgreSQL Global Development group. These don't work well together. In particular, you have psql from the ...
Laurenz Albe's user avatar
  • 50.2k
8 votes
Accepted

script to show all the permissions for a table

I found a good script to see the permissions on a database object. SELECT ( dp.state_desc + ' ' + dp.permission_name collate latin1_general_cs_as + ' ON ' + '[' + s.name + ']' + '.' + '[...
Marcello Miorelli's user avatar
8 votes
Accepted

SQL*Plus, @, and relative paths

Yup, this is Bug 2391334 which has been around for long time, and probably will not be fixed in the near future. One way of working around this is "know" the path for scripts without actually hard ...
Connor McDonald's user avatar
8 votes

What is the point of the Script Date in a stored procedure script generated by SSMS?

Why not? It allows you to see when the script was created if you save it & reload another time. The others you mention (created/last modified/last ran) aren't so useful as they're subject to ...
Gareth Lyons's user avatar
  • 1,148
8 votes
Accepted

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

The "X" is an alias for the following portion: Select distinct roleid from UserAccounts WHERE CustID = @custID So you'd be able to use it as: SELECT x.roleid FROM ( Select distinct roleid ...
SQLDevDBA's user avatar
  • 2,244
8 votes
Accepted

Scripting out database user level permissions

I use Script DB Level Permissions v3 (by S. Kusen) all the time to do what you are asking. Make sure you are executing in the database context you want to get perms from: DECLARE @sql VARCHAR(...
H.79's user avatar
  • 303
8 votes
Accepted

postgres create limited user that can select tables and columns of those tables

You can set column level privileges to very specifically (and narrowly) allow otherwise unprivileged users to access data. In your case, it is two catalogs: pg_attribute and pg_type. I have a user ...
András Váczi's user avatar
8 votes
Accepted

ERROR: "sql " is not a known variable

Your error message says: psql:cobertura.sql:29: ERROR: "sql " is not a known variable LINE 14: sql := format('insert into cobertura_tmp select count(*) as ... Look closely: "sql ", not "sql" That ...
Erwin Brandstetter's user avatar
7 votes
Accepted

Passwordless mysqldump via shell script in /etc/cron.daily

Solved with the help of this answer. cron doesn't know the path to ~/.my.cnf, so as per the MYSQL documentation you need to specify the path to the .my.cnf file. The proper passwordless mysqldump ...
Vila's user avatar
  • 223
7 votes
Accepted

Scripting a Maintenance Plan in SQL Server Express

You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you. The most common method is to use Windows Task Scheduler, but if you have any other job ...
LowlyDBA - John M's user avatar
6 votes
Accepted

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

I just wrote this earlier today. It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers. SET GLOBAL group_concat_max_len ...
vanyo's user avatar
  • 76
6 votes

script to show all the permissions for a table

I've got a couple of stored procedures you can use to display all of the permissions for a given database. Either for a single user/principal or for all of them. sp_dbpermissions and ...
Kenneth Fisher's user avatar
6 votes

Use sp_Blitz to gather information on all servers in a centralized server/database

Brent Ozar folks already have got you 50% - Inserting sp_Blitz® Output Into a Table My steps would be : Log the output to a local dbastats or some dba database. Have a list of all sql servers in ...
Kin Shah's user avatar
  • 62.2k
6 votes
Accepted

SSMS constraint script: Purpose of second alter table statement?

The second line is redundant but I agree the overuse of CHECK in that syntax is confusing. According to the ALTER TABLE documentation the existing data is checked anyway if the WITH CHECK ADD option ...
Tom V's user avatar
  • 15.7k
6 votes

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

I had similar problem. First turn on this option of SSMS - it is turned off by default: SSMS/Tools/Options/Query results/SQL Server/Results to grid/Retain CR/LF on copy or save Close SSMS, and run ...
Przemyslaw Remin's user avatar
6 votes
Accepted

How do I get Postgres DO $$ to work from the bash prompt?

Using \ to escape the dollar $ echo \$PATH $PATH Reference to psql doc, in order to execute multi commands, they also provide 3 ways to work around as below 1) Repeats -c option psql -c 'SELECT ...
Jacky's user avatar
  • 76
6 votes

how to script out the user defined table types?

Honestly, the amount of time you'll spend writing a version of this that accounts for all possible combinations of indexes, constraints, and default values, and troubleshoot all the combinations you ...
Aaron Bertrand's user avatar
5 votes

SQL Server 2012: Generate Scripts from command line

Just an update: in current versions of SQL Server powershell modules (SQL Server 2014 and on, I believed. Tested on SSMS 17), most of these options are native commands and methods. For instance, you ...
Victor Lopes's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible