Here is one way. It is slow and involves opening every table in SSMS.
Then you need to expand the table and then the columns to display each field.
One quick way is to use the SP_COLUMNS procedure. The easiest way to run it is to provide the table name that you wish to look for.
EXEC sp_columns @table_name = 'tbl_products'
The quickest way in my opinion is to use the INFORMATION_SCHEMA.COLUMNS view. In the query below, I am searching for the table name: tbl_products. This lists all of the columns and their data type.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'tbl_products'
Also with this view, you can search for only columns of a certain data type. In the example below, I want to see every table and column that has a data type of TEXT. With this query it is very simple.
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='text'
To read more about these Microsoft provided views/stored procedures, please follow the links below.
Often you have a list of values that you need to put into a query, but you only need certain columns of the data. I used to have to edit the data to get it in the format I needed, but now I can easily copy out the values I need using a single ALT key.
I had to setup a new server and I needed to copy all of the databases from the old server to it. This can take some time if you have many databases. Using Copy-DbaDatabase from dbatools makes this a simple process.
Using just one command, you can copy all of the databases from one server to another. It can use different methods, but I am going to use the USELASTBACKUP method. From the documentation: “This uses the last full, diff and logs instead of performing backups. Note that the backups must exist in a location accessible by all destination servers, such a network share.”
Here is the command I used to complete this task. I had to supply my source and destination server names. Since these servers were on a different network, I had to specify credentials. If I was using Windows Authentication, I would not have needed to supply credentials. The command below pops up a box for you to type in your password.
Copy-DbaDatabase -Source SERVERNAME\INSTANCE -Destination SERVERNAME\INSTANCE -BackupRestore -UseLastBackup -Force -SourceSqlCredential sa -DestinationSqlCredential sa -AllDatabases
When it is all complete, your screen should look like below with all showing a Successful status.
Looking in SSMS, it shows all of the databases being on the new server.
Now another very useful command from dbatools, is copying over all of the usernames/passwords from the old server to the new server. This is also a one line command. Using Copy-DbaLogin, you do not need to worry about orphan logins which can happen when restoring databases onto a new server. From the documentation: “SQL Server 2005 & newer: Migrates logins with SIDs, passwords, defaultdb, server roles & securables, database permissions & securables, login attributes (enforce password policy, expiration, etc.)”
Copy-DbaLogin -Source SERVERNAME\INSTANCE -Destination SERVERNAME\INSTANCE -SourceSqlCredential sa -DestinationSqlCredential sa
This will skip any user that already exists on the server.
Using dbatools and the commands Copy-DbaDatabase and Copy_DbaLogin migrating to new servers is a breeze. To read more about these commands please follow the links below. https://docs.dbatools.io/Copy-DbaDatabase.html
When you create subscriptions for your SSRS reports, it creates jobs in the SQL Server Agent with GUID’s when viewed in SSMS. This can make finding proper jobs more difficult if you have to scroll through hundreds or thousands of SSRS reports.
On my server I have over 500 report subscriptions along with other database jobs. How do I hide all of these? Keep reading to find out!
We will need to modify a system supplied stored procedure. You do this at your own risk. I do not accept responsibility if there are problems on your system after making this change. That said, I have been doing this for over 10 years without any problems.
Open up the MSDB database and go to Programmability and open up the dbo.sp_help_category stored procedure. Right click and choose modify.
We need to make two changes to this procedure. First, we need to find the @where_clause variable. Mine is on line #19 below.
We need to change the 500 to MAX as seen below.
Now we need to go to the bottom of the stored procedure and look for the final execute. On mine, it was line #97.
We will need to put this T-SQL code in front of the final execute and then execute the stored procedure to save it to the database.
-- Hide Reporting Services Jobs IN SSMS SET @where_clause += N' AND CASE WHEN name = ''Report Server'' AND ( SELECT program_name FROM sys.sysprocesses where spid = @@spid) = ''Microsoft SQL Server Management Studio'' THEN 0 ELSE 1 END = 1 '
Before:
After:
Now we can refresh the jobs.
After refreshing, all of the GUID jobs are no longer displaying. If you are still seeing the GUID jobs, make sure you executed the stored procedure to save it to the database.
One caveat: sometimes after patching, the GUID SSRS reports will reappear. Just bookmark this page and follow these steps again to fix.
There are times you have two tables that look the same and seem to have the same rows in each. Using the EXCEPT operator, you can find any rows that do not exist in either table.
Jim Salasek's SQL Server Blog 