T-SQL: Simplified CASE expression

My last article is providing in depth tutorial about these statements:

  1. ISNULL
  2. COALESCE
  3. IIF
  4. CHOOSE

The main purpose of introducing these statements is improving code readability and achieving cleaner code. Using these statements may result to poor performance in some situations. Therefore, I also discussed about alternative solutions. This article targets all levels of readers: from newbies to advanced.

You can find it in this link:

T-SQL: Simplified CASE expression

My next article will be about Error Handling in Triggers.

Posted in Best Practices, Joy of T-SQL, T-SQL Tips & Tricks

[Fixed] SSMS – Create script duplicates the statements for Grant or Deny Column Permissions…

I was reported a bug in Microsoft SQL Server (all versions) that related to generate script for database permissions on August 20, 2013.

Steps to Reproduce

Open SSMS >> Connect to Server >> In Object Explorer expand a Sample Database >> expand Security >> right click on Users >> click on New User… >> Fill needed data in General section >> click on Securables >> click on Search >> select a table and add it… >>
In Permission window in Explicit >> click on Select or Update >> Then click on Column Permission >> select Grant to one column and click OK
>>>>>>
Now If click on Script to generate its script, you can see the statements to grant or deny are duplicated in produced script.

Expected Results

non- duplicate
I’m happy to see that Microsoft fixed it soon on October 17, 2013. And the change will be included in a future release or servicing release for SQL Server. For more info please visit this link:
So, may need to install latest update!
Posted in Database Administration, SQL General Information

SQL SERVER BACKUP Database Permission

When speaking about Permission term in SQL SERVER , It has two parts:

  1. Server Permissions
  2. Database Permissions

And from another point of view, the SQL SERVER ROLE also has two parts:

  1. Server-Level Roles
  2. Database-Level Roles

In fact the Security Model of SQL SERVER is a hierarchical model. Therefore,

  • If a SQL SERVER Login has sysadmin Sever-Level role, can perform any activity on server like backup any databases.
  • If a SQL SERVER Login does not have sysadmin Sever-Level role, you have to map thisSQL Login to the database you want to use. This mapping automatically create a newDatabase User with the same name of the SQL Login.
  • Now you have two choices:
  • GRANT BACKUP permission to that Database User.
  • Add db_backupoperator Database-Level Role to that Database User.

For sample script see this link:

Backup rights

Another useful links:

Principals

Permissions Hierarchy

I wrote this post based on one MSDN Forum question. For more discussion see this motivated link :

Database Admin

Posted in Best Practices, Database Administration, SQL General Information, T-SQL Tips & Tricks

BULK INSERT multiple Files in SQL SERVER Express

This post comes from this MSDN Forum question.

Because of use SQL SERVER Express, we cannot use Integration Services(SSIS).

Therefore, we  have two choices:

  1. BULK INSERT
  2. OPENROWSET(BULK…)

In each statement you can insert only one column with above choices. Erland Sommarskog was provided a solution to insert multiple files in this link:

Bulk Insert Multiple Files (TSQL)

For more information you can also see this great article:

Bulk Inserts via TSQL in SQL Server

Posted in Database Administration, SQL General Information, T-SQL Tips & Tricks

INFORMATION_SCHEMA.ROUTINE_COLUMNS

This query shows the Code behind  of INFORMATION_SCHEMA.ROUTINE_COLUMNS.

SELECT DB_NAME() AS TABLE_CATALOG
	,SCHEMA_NAME(o.schema_id) AS TABLE_SCHEMA
	,o.NAME AS TABLE_NAME
	,c.NAME AS COLUMN_NAME
	,c.column_id AS ORDINAL_POSITION
	,CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)) AS COLUMN_DEFAULT
	,CONVERT(VARCHAR(3), CASE 
			WHEN c.is_nullable = 1
				THEN 'YES'
			ELSE 'NO'
			END) AS IS_NULLABLE
	,ISNULL(TYPE_NAME(c.system_type_id), t.NAME) AS DATA_TYPE
	,COLUMNPROPERTY(c.object_id, c.NAME, 'charmaxlen') AS CHARACTER_MAXIMUM_LENGTH
	,COLUMNPROPERTY(c.object_id, c.NAME, 'octetmaxlen') AS CHARACTER_OCTET_LENGTH
	,CONVERT(TINYINT, CASE -- int/decimal/numeric/real/float/money
			WHEN c.system_type_id IN (
					48
					,52
					,56
					,59
					,60
					,62
					,106
					,108
					,122
					,127
					)
				THEN c.precision
			END) AS NUMERIC_PRECISION
	,CONVERT(SMALLINT, CASE -- int/money/decimal/numeric
			WHEN c.system_type_id IN (
					48
					,52
					,56
					,60
					,106
					,108
					,122
					,127
					)
				THEN 10
			WHEN c.system_type_id IN (
					59
					,62
					)
				THEN 2
			END) AS NUMERIC_PRECISION_RADIX
	,-- real/float
	CONVERT(INT, CASE -- datetime/smalldatetime
			WHEN c.system_type_id IN (
					40
					,41
					,42
					,43
					,58
					,61
					)
				THEN NULL
			ELSE odbcscale(c.system_type_id, c.scale)
			END) AS NUMERIC_SCALE
	,CONVERT(SMALLINT, CASE -- datetime/smalldatetime
			WHEN c.system_type_id IN (
					40
					,41
					,42
					,43
					,58
					,61
					)
				THEN odbcscale(c.system_type_id, c.scale)
			END) AS DATETIME_PRECISION
	,CONVERT(SYSNAME, NULL) AS CHARACTER_SET_CATALOG
	,CONVERT(SYSNAME, NULL) AS CHARACTER_SET_SCHEMA
	,CONVERT(SYSNAME, CASE 
			WHEN c.system_type_id IN (
					35
					,167
					,175
					) -- char/varchar/text
				THEN COLLATIONPROPERTY(c.collation_name, 'sqlcharsetname')
			WHEN c.system_type_id IN (
					99
					,231
					,239
					) -- nchar/nvarchar/ntext
				THEN N'UNICODE'
			END) AS CHARACTER_SET_NAME
	,CONVERT(SYSNAME, NULL) AS COLLATION_CATALOG
	,CONVERT(SYSNAME, NULL) AS COLLATION_SCHEMA
	,c.collation_name AS COLLATION_NAME
	,CONVERT(SYSNAME, CASE 
			WHEN c.user_type_id > 256
				THEN DB_NAME()
			END) AS DOMAIN_CATALOG
	,CONVERT(SYSNAME, CASE 
			WHEN c.user_type_id > 256
				THEN SCHEMA_NAME(t.schema_id)
			END) AS DOMAIN_SCHEMA
	,CONVERT(SYSNAME, CASE 
			WHEN c.user_type_id > 256
				THEN TYPE_NAME(c.user_type_id)
			END) AS DOMAIN_NAME
FROM sys.objects o
JOIN sys.columns c ON c.object_id = o.object_id
LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE o.type IN (
		'TF'
		,'IF'
		,'FT'
		)
Posted in Database Administration, Information Schema | Leave a comment

INFORMATION_SCHEMA.COLUMN_PRIVILEGES

The next script is the Code behind of the INFORMATION_SCHEMA.COLUMN_PRIVILEGES view.

SELECT USER_NAME(p.grantor_principal_id) AS GRANTOR
	,USER_NAME(p.grantee_principal_id) AS GRANTEE
	,DB_NAME() AS TABLE_CATALOG
	,SCHEMA_NAME(o.schema_id) AS TABLE_SCHEMA
	,o.NAME AS TABLE_NAME
	,c.NAME AS COLUMN_NAME
	,CONVERT(VARCHAR(10), CASE p.type
			WHEN 'SL'
				THEN 'SELECT'
			WHEN 'UP'
				THEN 'UPDATE'
			WHEN 'RF'
				THEN 'REFERENCES'
			END) AS PRIVILEGE_TYPE
	,CONVERT(VARCHAR(3), CASE p.STATE
			WHEN 'G'
				THEN 'NO'
			WHEN 'W'
				THEN 'YES'
			END) AS IS_GRANTABLE
FROM sys.database_permissions p
	,sys.objects o
	,sys.columns c
WHERE o.type IN (
		'U'
		,'V'
		)
	AND o.object_id = c.object_id
	AND p.class = 1
	AND p.major_id = o.object_id
	AND p.minor_id = c.column_id
	AND p.type IN (
		'RF'
		,'SL'
		,'UP'
		)
	AND p.STATE IN (
		'G'
		,'W'
		)
	AND (
		p.grantee_principal_id = 0
		OR p.grantee_principal_id = DATABASE_PRINCIPAL_ID()
		OR p.grantor_principal_id = DATABASE_PRINCIPAL_ID()
		)
Posted in Database Administration, Information Schema | Leave a comment

COLUMN_DOMAIN_USAGE

Information Schema Views Code behind (INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE)!!!


SELECT  DB_NAME() AS DOMAIN_CATALOG ,
        SCHEMA_NAME(t.schema_id) AS DOMAIN_SCHEMA ,
        t.name AS DOMAIN_NAME ,
        DB_NAME() AS TABLE_CATALOG ,
        SCHEMA_NAME(o.schema_id) AS TABLE_SCHEMA ,
        o.name AS TABLE_NAME ,
        c.name AS COLUMN_NAME
FROM    sys.objects o
        JOIN sys.columns c ON c.object_id = o.object_id
        JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE   c.user_type_id > 256

Posted in Database Administration, Information Schema | Leave a comment

REFERENTIAL_CONSTRAINTS

Information Schema Views Code behind (INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS)!!!


SELECT  DB_NAME() AS CONSTRAINT_CATALOG ,
        SCHEMA_NAME(f.schema_id) AS CONSTRAINT_SCHEMA ,
        f.name AS CONSTRAINT_NAME ,
        DB_NAME() AS UNIQUE_CONSTRAINT_CATALOG ,
        SCHEMA_NAME(t.schema_id) AS UNIQUE_CONSTRAINT_SCHEMA ,
        i.name AS UNIQUE_CONSTRAINT_NAME ,
        CONVERT(VARCHAR(7), 'SIMPLE') AS MATCH_OPTION ,
        CONVERT(VARCHAR(11), CASE f.update_referential_action
                               WHEN 0 THEN 'NO ACTION'
                               WHEN 1 THEN 'CASCADE'
                               WHEN 2 THEN 'SET NULL'
                               WHEN 3 THEN 'SET DEFAULT'
                             END) AS UPDATE_RULE ,
        CONVERT(VARCHAR(11), CASE f.delete_referential_action
                               WHEN 0 THEN 'NO ACTION'
                               WHEN 1 THEN 'CASCADE'
                               WHEN 2 THEN 'SET NULL'
                               WHEN 3 THEN 'SET DEFAULT'
                             END) AS DELETE_RULE
FROM    sys.foreign_keys f
        LEFT JOIN sys.indexes i ON i.object_id = f.referenced_object_id
                                   AND i.index_id = f.key_index_id
        LEFT JOIN sys.tables t ON t.object_id = f.referenced_object_id


Posted in Database Administration, Information Schema | Leave a comment

CHECK_CONSTRAINTS

Information Schema Views Code behind (INFORMATION_SCHEMA.CHECK_CONSTRAINTS)!!!

SELECT  DB_NAME() AS CONSTRAINT_CATALOG ,
        SCHEMA_NAME(schema_id) AS CONSTRAINT_SCHEMA ,
        name AS CONSTRAINT_NAME ,
        CONVERT(NVARCHAR(4000), definition) AS CHECK_CLAUSE
FROM    sys.check_constraints
Posted in Database Administration, Information Schema | Leave a comment

COLUMNS

Information Schema Views Code behind (INFORMATION_SCHEMA.COLUMNS)!!!

SELECT  DB_NAME() AS TABLE_CATALOG ,
        SCHEMA_NAME(o.schema_id) AS TABLE_SCHEMA ,
        o.name AS TABLE_NAME ,
        c.name AS COLUMN_NAME ,
        COLUMNPROPERTY(c.object_id, c.name, 'ordinal') AS ORDINAL_POSITION ,
        CONVERT(NVARCHAR(4000), OBJECT_DEFINITION(c.default_object_id)) AS COLUMN_DEFAULT ,
        CONVERT(VARCHAR(3), CASE c.is_nullable
                              WHEN 1 THEN 'YES'
                              ELSE 'NO'
                            END) AS IS_NULLABLE ,
        ISNULL(TYPE_NAME(c.system_type_id), t.name) AS DATA_TYPE ,
        COLUMNPROPERTY(c.object_id, c.name, 'charmaxlen') AS CHARACTER_MAXIMUM_LENGTH ,
        COLUMNPROPERTY(c.object_id, c.name, 'octetmaxlen') AS CHARACTER_OCTET_LENGTH ,
        CONVERT(TINYINT, CASE -- int/decimal/numeric/real/float/money
                              WHEN c.system_type_id IN ( 48, 52, 56, 59,
                                                         60, 62, 106, 108,
                                                         122, 127 )
                              THEN c.precision
                         END) AS NUMERIC_PRECISION ,
        CONVERT(SMALLINT, CASE	-- int/money/decimal/numeric
                               WHEN c.system_type_id IN ( 48, 52, 56, 60,
                                                          106, 108, 122,
                                                          127 ) THEN 10
                               WHEN c.system_type_id IN ( 59, 62 ) THEN 2
                          END) AS NUMERIC_PRECISION_RADIX ,	-- real/float
        CONVERT(INT, CASE	-- datetime/smalldatetime
                          WHEN c.system_type_id IN ( 40, 41, 42, 43, 58,
                                                     61 ) THEN NULL
                          ELSE odbcscale(c.system_type_id, c.scale)
                     END) AS NUMERIC_SCALE ,
        CONVERT(SMALLINT, CASE -- datetime/smalldatetime
                               WHEN c.system_type_id IN ( 40, 41, 42, 43,
                                                          58, 61 )
                               THEN odbcscale(c.system_type_id, c.scale)
                          END) AS DATETIME_PRECISION ,
        CONVERT(SYSNAME, NULL) AS CHARACTER_SET_CATALOG ,
        CONVERT(SYSNAME, NULL) AS CHARACTER_SET_SCHEMA ,
        CONVERT(SYSNAME, CASE WHEN c.system_type_id IN ( 35, 167, 175 )	-- char/varchar/text
                                   THEN COLLATIONPROPERTY(c.collation_name,
                                                          'sqlcharsetname')
                              WHEN c.system_type_id IN ( 99, 231, 239 )	-- nchar/nvarchar/ntext
                                   THEN N'UNICODE'
                         END) AS CHARACTER_SET_NAME ,
        CONVERT(SYSNAME, NULL) AS COLLATION_CATALOG ,
        CONVERT(SYSNAME, NULL) AS COLLATION_SCHEMA ,
        c.collation_name AS COLLATION_NAME ,
        CONVERT(SYSNAME, CASE WHEN c.user_type_id > 256 THEN DB_NAME()
                         END) AS DOMAIN_CATALOG ,
        CONVERT(SYSNAME, CASE WHEN c.user_type_id > 256
                              THEN SCHEMA_NAME(t.schema_id)
                         END) AS DOMAIN_SCHEMA ,
        CONVERT(SYSNAME, CASE WHEN c.user_type_id > 256
                              THEN TYPE_NAME(c.user_type_id)
                         END) AS DOMAIN_NAME
FROM    sys.objects o
        JOIN sys.columns c ON c.object_id = o.object_id
        LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE   o.type IN ( 'U', 'V' )

Posted in Database Administration, Information Schema | Leave a comment