Tag: TSQL

T-SQL

Using STRING_AGG to Return Schema Information

A few weeks ago, someone came to me wanting a dump of tables in a database and all the columns as well. They were working on some data governance and the first step was to know roughly what existed in the database.

After a chat, we decided what they needed was a list of columns and all the tables where those columns were used and another list of tables with a list of the columns used in the table. To help import the data into their tooling they needed a comma separated list of the tables for each column and the columns for each table.

After spending many years using various forms of FOR XML to do this sort of thing, I decided it was time to make my life easier and use STRING_AGG instead.

STRING_AGG can take a set of data and use the provided delimiter to create a list. As a bonus, you can also use the WITHIN GROUP to order the list of items within the STRING_AGG statement.

Here’s a quick example of I used STRING_AGG to return the table and column data requested by the governance folks.

/* Return a list columns in the database and for each column a comma delimited list of tables in which that column appears */
SELECT
c.name AS ColumnName
, STRING_AGG(CONCAT( QUOTENNAME(s.name), '.', QUOTENAME(t.name)), ', ')
WITHIN GROUP (ORDER BY CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name))) AS TableListForColumn
, COUNT(*) AS TableUsageCountForColumn
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY c.name
ORDER BY TableUsageCountForColumn DESC
, ColumnName ASC;
/* Return a list of tables in a database and for each table a comma delimited list of columns in that table */
SELECT
CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name)) AS TableName
, STRING_AGG(c.name, ', ' )
WITHIN GROUP (ORDER BY c.name) AS ColumnList
, COUNT(*) AS CountColumnsInTable
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY concat(quotename(s.name), '.', quotename(t.name))
ORDER BY CountColumnsInTable DESC
, TableName ASC
view raw GetSqlServerColumnUse.sql hosted with ❤ by GitHub
SQL

Logon Triggers Do Not Prevent SSMS Connections

I recently saw a post on StackExchange where a user was having a problem with tempdb filling up, which was causing their logon trigger to fail.

The logon trigger was attempting to prevent users from connecting with SQL Server Management Studio (SSMS). It seems that the user was not aware that it is trivial to connect using SSMS to SQL bypassing the app level restriction in place.

Let’s look at an example.

First we’ll create the logon trigger that explicitly looks to see if the application attempting to logon is management studio:

CREATE TRIGGER [DenySSMSLogin] ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE @app SYSNAME = APP_NAME();
IF @app LIKE N'%Management Studio%'
BEGIN
THROW 51000, 'Connection not allowed.', 1;
END;
END;
GO

ENABLE TRIGGER [DenySSMSLogin] ON ALL SERVER;
GO

 

Now we can test this by opening a new window. We should see an error:

failed-to-logon

Looks like it’s working just fine.

So let’s circumvent this, try connecting again, this time we just need to edit the Additional Connection Parameters in the connection dialog and pass along a new application name.

additional-connection-parameters

And now the connection will work just fine, completely bypassing the trigger (if trying this, don’t forget to disable your trigger when done).

SQL

Formatting Number Output

I’ve been working with some large data load processes recently and have been dumping upwards of a billion records into tables. As these are not instantaneous loads I found myself wanting to keep an eye on how much data was loaded.

This is nice and easy to do using the sys.partitions system view. Here’s an example from AdventureWorks

SELECT t.name, rows

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC;

top+10

This is all good and well, but when you start getting to large values on the tables it becomes a little difficult to tell the difference between 31 million and 312 million. That’s when I resort back to using commas. Unfortunately SQL Server does not make this easy, although it can be accomplished with a couple of converts.

SELECT t.name, substring(convert(varchar, convert(money, rows), 1), 1, len(convert(varchar, convert(money, rows), 1))-3)

FROM sys.partitions p

   INNER JOIN sys.tables t

       ON p.OBJECT_ID = t.OBJECT_ID

WHERE p.index_id IN (0,1)

ORDER BY rows DESC

top+10+2

This is still a little arduous to type, so I created a function to do the work. Now it’s just a case of calling that function whenever I want to have the numbers presented to me in a much more readable format.

CREATE FUNCTION dbo.fnNumberComma (@iVal bigint)

RETURNS VARCHAR(30)

AS

BEGIN

DECLARE @oVal VARCHAR(30)





SELECT @oVal = SUBSTRING(CONVERT(VARCHAR(30), CONVERT(money, @iVal),1),1,LEN(CONVERT(VARCHAR(30),CONVERT(money,@iVal),1))-3)




RETURN (@oVal)




END




GO












SELECT t.name, dbo.fnNumberComma(rows) as [RowCount]




FROM sys.partitions p




   INNER JOIN sys.tables t




       ON p.OBJECT_ID = t.OBJECT_ID




WHERE p.index_id IN (0,1)




ORDER BY rows DESC;








top+10+3


	


	



			
				


	

	

	

		

						

														
						SQL					
									
							

			
			
Extended Properties Are Your Friend

		

	



	

	


It’s nice to have friends, why aren’t you a friend of extended properties? They can make your like so much easier by helping to document your databases and objects.


Take a basic table create statement






CREATE TABLE [dbo].[EatSomeBoogers](




    [ID] [int] NOT NULL,




    [BgType] [varchar](20) NOT NULL,




    [Size] [smallint] NULL,




    [Viscosity] [tinyint] NULL




) ON [PRIMARY]




 




GO






 


Pretty simple and we can infer a lot of the information about the table, but we mention size, are we measuring in inches, feet, yards?


We can add extended properties that will help to provide that sort of guidance for someone to reference later on.






EXEC sys.sp_addextendedproperty @name = N'Details',




    @value = N'Size is measured in mm', @level0type = N'SCHEMA',




    @level0name = N'dbo', @level1type = N'TABLE',




    @level1name = N'EatSomeBoogers', @level2type = N'COLUMN',




    @level2name = N'Size'




GO






We can also add properties at the table level:






EXEC sys.sp_addextendedproperty @name = N'Purpose',




    @value = N'Holds information about all the gold digging’,




    @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',




    @level1name = N'EatSomeBoogers'




GO






And at the database level:






EXEC [MyDB].sys.sp_addextendedproperty @name = N'Usage',




    @value = N'Will handle all information as relates to digging' 




GO






 


You can even add extended properties to other objects, like stored procedures:






EXEC sys.sp_addextendedproperty @name = N'ProcUsage',




    @value = N'Gets booger sizes and types', @level0type = N'SCHEMA',




    @level0name = N'dbo', @level1type = N'PROCEDURE',




    @level1name = N'GrabBoogers'




GO






 


What’s great is that you can then quickly and easily query the extended properties for your objects:






SELECT  




        OBJECT_NAME(ep.major_id) AS ObjectName ,




        CASE 




        WHEN c.name IS NOT NULL then 'COLUMN'




        else o.type_desc




        END AS ExtendedPropertyType,




        c.name AS 'ColumnName' ,




        ep.name AS ExtendedPropertyName ,




        ep.value AS ExtendedPropertyValue




FROM    sys.extended_properties ep




        LEFT JOIN sys.columns c ON ep.major_id = c.object_id




                                   AND ep.minor_id = c.column_id




        LEFT JOIN sys.objects o ON ep.major_id = o.object_id




ORDER BY ObjectName, ColumnName






image


 


 


This give you a really quick and easy way to document your objects. I highly recommend that during your next development project that you make life easy for yourself and add extended properties to your objects (and trust me, your DBA will thank you).




	


	



			
				


	

	

	

		

						

														
						SQL					
									
							

			
			
Speedy Log Shipping Setup

		

	



	

	


As a follow up to last weeks post on quickly restoring multiple databases to default locations I’ve another script which might help you with quick deployments.


Last week I covered restoring the databases, I also needed to get log shipping setup for multiple databases. If you’ve ever had to configure log shipping you know that there’s lots of clicking and typing of things and entering information. It’s a bit of a pain for a single database, when you have to set it up for multiple databases it’s a long, slow, time consuming nightmare.


I’d had enough of dealing with this so I put together a script that will do the complete configuration of the primary and secondary servers with a single execution. The key to doing this is using SQLCMD mode from within SSMS.


Never used SQLCMD mode before? I’m not surprised, it’s not that well known. To enter SQLCMD mode you either choose the option from the Query menu item or add the button to one of your tool bars


image


This allows you to do some special things, like parameter substitution and, key to what I’m doing, change connections as a part of the script.


Try the following, replacing PrimaryServer and SecondaryServer with something relevant to your environment.


image


As you can see from the script it connected to each server in turn and grabbed the @@SERVERNAME value. That’s pretty special.


My log shipping script does very much the same thing. You just provide the primary and secondary servers, the file locations for the transaction log backups and the name of the database. The script takes care of the rest.


It will perform a very basic check to ensure that the local backup directory exists and error if not. The script also does not encompass a log shipping monitor server and has a default of 15 minutes for the backup/copy/restore processes. All of that could be quickly adjusted and made additional parameters.


View the script below or download SetupLogShipping.sql


Of course this doesn’t answer the question of when or why you would use log shipping, that’s for another post.






:ON ERROR exit




 




:setvar PrimaryServer "ServerA"




:setvar PrimaryDatabase "MyDB"




:setvar PrimaryBackupDirectory "E:BackupDBTrxLogs"




:setvar PrimaryBackupShare "ServerAtrxlogs$"




:setvar SecondaryServer "ServerB"




:setvar SecondaryDatabase "MyDB"




:setvar SecondaryBackupDirectory "ServerBTrxlogs$TrxForRestore"




 




SET NOCOUNT ON




/* Begin: Script to be run at Primary */




 




 




:CONNECT $(PrimaryServer)




 




BEGIN




USE msdb;




 




/* Check to see if the backup folder exists */




RAISERROR('Checking to see if $(PrimaryBackupDirectory) exists...',0,1) WITH NOWAIT




DECLARE @CheckDir NVARCHAR(2000)




DECLARE @ErrorMsg NVARCHAR(200)




DECLARE @DirInfo TABLE




    (




      FileExists BIT ,




      FileIsADirectory BIT ,




      ParentDirectoryExists BIT




    )




                                




SET @CheckDir = '$(PrimaryBackupDirectory)'




 




INSERT  INTO @DirInfo




        ( FileExists ,




          FileIsADirectory ,




          ParentDirectoryExists




        )




        EXECUTE [master].dbo.xp_fileexist @CheckDir




 




 




 




IF NOT EXISTS ( SELECT  *




                FROM    @DirInfo




                WHERE   FileExists = 0




                        AND FileIsADirectory = 1




                        AND ParentDirectoryExists = 1 ) 




    BEGIN




        SET @ErrorMsg = 'The primary backup directory $(PrimaryBackupDirectory) does not exist...quitting' 




        RAISERROR(@ErrorMsg,16,1) WITH NOWAIT




        RETURN




    END




RAISERROR('$(PrimaryBackupDirectory) exists, continuing...',0,1) WITH NOWAIT  




 




DECLARE @LS_BackupJobId    AS uniqueidentifier 




DECLARE @LS_PrimaryId    AS uniqueidentifier 




DECLARE @SP_Add_RetCode    As int 




 




RAISERROR('Enabling log shipping for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT




 




EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database 




        @database = N'$(PrimaryDatabase)' 




        ,@backup_directory = N'$(PrimaryBackupDirectory)' 




        ,@backup_share = N'$(PrimaryBackupShare)' 




        ,@backup_job_name = N'LSBackup_$(PrimaryDatabase)' 




        ,@backup_retention_period = 4320




        ,@backup_compression = 1




        ,@backup_threshold = 60 




        ,@threshold_alert_enabled = 1




        ,@history_retention_period = 5760 




        ,@backup_job_id = @LS_BackupJobId OUTPUT 




        ,@primary_id = @LS_PrimaryId OUTPUT 




        ,@overwrite = 1 




 




 




IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) 




BEGIN 




 




DECLARE @LS_BackUpScheduleUID    As uniqueidentifier 




DECLARE @LS_BackUpScheduleID    AS int 




 




 




EXEC msdb.dbo.sp_add_schedule 




        @schedule_name =N'LSBackupSchedule_$(PrimaryServer)' 




        ,@enabled = 1 




        ,@freq_type = 4 




        ,@freq_interval = 1 




        ,@freq_subday_type = 4 




        ,@freq_subday_interval = 15 




        ,@freq_recurrence_factor = 0 




        ,@active_start_date = 20120607 




        ,@active_end_date = 99991231 




        ,@active_start_time = 0 




        ,@active_end_time = 235900 




        ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT 




        ,@schedule_id = @LS_BackUpScheduleID OUTPUT 




 




EXEC msdb.dbo.sp_attach_schedule 




        @job_id = @LS_BackupJobId 




        ,@schedule_id = @LS_BackUpScheduleID  




 




EXEC msdb.dbo.sp_update_job 




        @job_id = @LS_BackupJobId 




        ,@enabled = 1 




 




 




END 




 




 




EXEC master.dbo.sp_add_log_shipping_alert_job 




 




EXEC master.dbo.sp_add_log_shipping_primary_secondary 




        @primary_database = N'$(PrimaryDatabase)' 




        ,@secondary_server = N'$(PrimaryServer)' 




        ,@secondary_database = N'$(SecondaryDatabase)' 




        ,@overwrite = 1 




 




 




RAISERROR('Log shipping enabled for $(PrimaryDatabase) on $(PrimaryServer)...',0,1) WITH NOWAIT




END




GO




/* ENDS THE PRIMARY SCRIPT */




 




 




 




/* Script to be run on secondary */




:CONNECT $(SecondaryServer)




USE msdb;




 




DECLARE @LS_Secondary__CopyJobId    AS uniqueidentifier 




DECLARE @LS_Secondary__RestoreJobId    AS uniqueidentifier 




DECLARE @LS_Secondary__SecondaryId    AS uniqueidentifier 




DECLARE @LS_Add_RetCode    As int 




 




RAISERROR('Adding log shipping secondary to $(SecondaryServer) with database name $(SecondaryDatabase)...',0,1) WITH NOWAIT




 




EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 




        @primary_server = N'$(PrimaryServer)' 




        ,@primary_database = N'$(PrimaryDatabase)' 




        ,@backup_source_directory = N'$(PrimaryBackupShare)' 




        ,@backup_destination_directory = N'$(SecondaryBackupDirectory)' 




        ,@copy_job_name = N'LSCopy_$(PrimaryServer)_$(PrimaryDatabase)' 




        ,@restore_job_name = N'LSRestore_$(PrimaryServer)_$(PrimaryDatabase)' 




        ,@file_retention_period = 4320 




        ,@overwrite = 1 




        ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 




        ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 




        ,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 




 




IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 




BEGIN 




 




DECLARE @LS_SecondaryCopyJobScheduleUID    As uniqueidentifier 




DECLARE @LS_SecondaryCopyJobScheduleID    AS int 




 




RAISERROR('Creating log shipping secondary jobs...',0,1) WITH NOWAIT




 




EXEC msdb.dbo.sp_add_schedule 




        @schedule_name =N'DefaultCopyJobSchedule' 




        ,@enabled = 1 




        ,@freq_type = 4 




        ,@freq_interval = 1 




        ,@freq_subday_type = 4 




        ,@freq_subday_interval = 15 




        ,@freq_recurrence_factor = 0 




        ,@active_start_date = 20120607 




        ,@active_end_date = 99991231 




        ,@active_start_time = 0 




        ,@active_end_time = 235900 




        ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 




        ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 




 




EXEC msdb.dbo.sp_attach_schedule 




        @job_id = @LS_Secondary__CopyJobId 




        ,@schedule_id = @LS_SecondaryCopyJobScheduleID  




 




DECLARE @LS_SecondaryRestoreJobScheduleUID    As uniqueidentifier 




DECLARE @LS_SecondaryRestoreJobScheduleID    AS int 




 




 




EXEC msdb.dbo.sp_add_schedule 




        @schedule_name =N'DefaultRestoreJobSchedule' 




        ,@enabled = 1 




        ,@freq_type = 4 




        ,@freq_interval = 1 




        ,@freq_subday_type = 4 




        ,@freq_subday_interval = 15 




        ,@freq_recurrence_factor = 0 




        ,@active_start_date = 20120607 




        ,@active_end_date = 99991231 




        ,@active_start_time = 0 




        ,@active_end_time = 235900 




        ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 




        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 




 




EXEC msdb.dbo.sp_attach_schedule 




        @job_id = @LS_Secondary__RestoreJobId 




        ,@schedule_id = @LS_SecondaryRestoreJobScheduleID  




 




 




END 




 




 




DECLARE @LS_Add_RetCode2    As int 




 




 




IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 




BEGIN 




 




EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 




        @secondary_database = N'$(SecondaryDatabase)' 




        ,@primary_server = N'$(PrimaryServer)' 




        ,@primary_database = N'$(PrimaryDatabase)' 




        ,@restore_delay = 0 




        ,@restore_mode = 0 




        ,@disconnect_users    = 0 




        ,@restore_threshold = 45   




        ,@threshold_alert_enabled = 1 




        ,@history_retention_period    = 5760 




        ,@overwrite = 1 




 




END 




 




 




IF (@@error = 0 AND @LS_Add_RetCode = 0) 




BEGIN 




 




EXEC msdb.dbo.sp_update_job 




        @job_id = @LS_Secondary__CopyJobId 




        ,@enabled = 1 




 




EXEC msdb.dbo.sp_update_job 




        @job_id = @LS_Secondary__RestoreJobId 




        ,@enabled = 1 




 




END 




 




RAISERROR('Setup of log shipping from database $(PrimaryDatabase) on server $(PrimaryServer) to database $(SecondaryDatabase) on $(SecondaryServer) complete!',0,1) WITH NOWAIT




 




 




 








	


	



			
				


	

	

	

		

						

														
						SQL					
									
							

			
			
Who Owns Your Databases And Jobs?

		

	



	

	


Quick question, who owns your databases? And how about your jobs? Don’t know? Go find out…now find out without using the GUI.


Querying sys.databases will give you the owner_sid. The trouble is that you have to relate that owner_sid back to a user. The same with sysjobs, that sid needs to relate to someone.


You could attempt joining against sys.server_principals, however if there’s a user that doesn’t exist or is a part of a domain group they wouldn’t show up and you would just get a NULL returned. Try the following example:






SELECT  d.NAME ,




        sp.NAME




FROM    sys.databases d




        LEFT JOIN sys.server_principals sp ON d.owner_sid = sp.sid;




        




SELECT  s.NAME ,




        sp.NAME




FROM    msdb.dbo.sysjobs s




        LEFT OUTER JOIN sys.server_principals sp ON s.owner_sid = sp.sid ;     






 


Now what, how can you quickly and easily get the owner? A little known system function will come to the rescue.


SUSER_SNAME (server_user_sid) will return the login name associated with a SID. Using this you can very quickly grab the information about who owns what without having to play with joins or get incomplete results:






SELECT  name ,




        SUSER_SNAME(owner_sid)




FROM    sys.databases;




 




SELECT  name ,




        SUSER_SNAME(owner_sid)




FROM    msdb.dbo.sysjobs;






 


There’s some great little hidden gems lying around in SQL Server like this. You just need to find them.




	


	



			
				


	

	

	

		

						

														
						SQL, SQL 2012					
									
							

			
			
SQL2012 It’s The Small Things Pt3–End Of The Month Function

		

	



	

	


If you work with financial data or have to do regular reporting type functions then I’m sure you currently use some kind of funky function/case statement to figure out what the last day of the month is and something even more funky to find the last day of next month or the month after.


SQL Server 2012 fixes that for you with the introduction of the EOMONTH function.


It’s very simple to use:






SELECT EOMONTH('2012/03/16')






image


 


Even better, the EOMONTH function can accept an additional parameter where you can specify a number of months to add or remove to get relevant results:






SELECT EOMONTH('2012/03/16', -1)






image


 


It even handled the leap year correctly.


Simplicity itself. A great addition.