:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Feb 23 / 2012
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Select and Insert rows in same table with some different values

A requirement may arise when we have to select specific rows and insert these in same table with some values different. There may be several scenarios for it. Following example would cover the requirement and process involved to complete the task.
We have a table with information about language skills of employees. We are required to select records of an employee and insert these for different employee. All other field values are same except the employee number that we would provide explicitly in script. Consider following script

 
-- Craete table for demo  
 CREATE TABLE EmpLanguages  
 (ID SMALLINT IDENTITY(1,1),  
 CNIC CHAR(13), Lang VARCHAR(50),  
 OralSkill TINYINT, WrittenSkill TINYINT)  
 GO  
 -- Populate table with sample data  
 INSERT INTO EmpLanguages  
 SELECT '1111111111111','Urdu', 4, 4  
 UNION ALL  
 SELECT '1111111111111','Arabic', 3, 2  
 UNION ALL  
 SELECT '1111111111111','English', 3, 3  
 UNION ALL  
 SELECT '1111111111111','Pashto', 3, 2  
 GO  
 -- verify the data  
 SELECT * FROM EmpLanguages  
 GO  

We have values for employee with CNIC 1111111111111, that we have to insert for employee with CNIC 2222222222222. First of all get columns list instead of manually writing these in query

 -- Get columns  
 SP_HELP EmpLanguages  
 GO  

Now copy the columns and construct following script.

 -- Insert same records for different employees  
 INSERT INTO EmpLanguages  
 SELECT '2222222222222' ,Lang, OralSkill, WrittenSkill  
 FROM EmpLanguages  
 WHERE CNIC = '1111111111111'  
 GO  
 -- Again verify the data  
 SELECT * FROM EmpLanguages  
 GO  

Verification of data shows that task is complete. This method may be applied for various other scenarios with little modification.

  • Feb 16 / 2012
  • 0
dbDigger, Logins and Users, Logon Triggers, Security and Permissions, T-SQL Scripts, T-SQL Tips and Tricks, Triggers

Restrict logon for a SQL Server login through all but one IP

In a previous article i discussed that how to restrict SQL Server login to connect from out side the application. Logon trigger was utilize to achieve this task. The puprose behind it was to make sure that permission rich logins used in application should not be allowed to access SQL Server by any way other than managed application code.
However it is possible that any one may use that login through application for unit testing or to execute ad-hoc code from inside the application code. I was asked to also restrict such access that is adhoc or for unit testing from developers desktops. So the requirement is that login used in application could only access SQL Server after fulfilling two conditions

  1. Login is requesting to connect from inside the application code
  2. Login is requesting from no where else but web server (application server)

My previous article covers the first requirement but it lacks to check the second requirement. Following code (modified version of previous article) would make sure to check both the conditions prior to granting login access

 
CREATE TRIGGER [RestrictSSMSLogIn]  
 ON ALL SERVER WITH EXECUTE AS 'AppUser'  
 FOR LOGON  
 AS  
 BEGIN  
 DECLARE @ip VARCHAR(16)  
 SELECT @ip = client_net_address  
 FROM sys.dm_exec_connections WHERE session_id = @@SPID  
 IF ORIGINAL_LOGIN()= 'AppUser' AND  
 ((SELECT TOP 1 Program_Name  
 FROM sys.dm_exec_sessions  
 WHERE is_user_process = 1  
 AND original_login_name = 'AppUser'  
 Order By Session_Id Desc)  
 <>'.Net SqlClient Data Provider'  
 or @ip <> '192.168.1.202') -- Provide IP address of web server here  
 ROLLBACK;  
 END  
 GO  
 ENABLE TRIGGER [RestrictSSMSLogIn] ON ALL SERVER  
 GO  

In this way the login used for application is restricted to access only from web server through application code only.

  • Dec 05 / 2011
  • 0
Data Modeling and Database Design, dbDigger, Monitoring and Analysis, T-SQL Scripts

Analyze all foreign keys in your SQL Server database

Creating foreign keys ensure data integrity across different tables in a database. However only creating these is not enough. Keeping in view the requirement we have to configure proper impact on foreign key value when its primary key is affected by update or delete operation.
SQL Server provides four rules for implementing the effect of primary key on foreign key value in case of update or delete operation. These are

  • No Action
  • Cascade
  • Set NULL
  • Set Default

In my related article on MSSQLTips, I have discussed the configuration and effect of these four rules. The emerged problem is to analyze the existing foreign keys for their configured rules for update and delete operations. It is required to generate a list of all foreign keys along with their associated columns and rules for update and delete operations. Below is the script that would provide a brief information to start with

 
SELECT fkey.name AS FKeyName,  
 object_name(fkey.parent_object_id) AS FKeyTable,  
 cols.name AS FKeyColumn,  
 object_name(fkey.referenced_object_id) AS PKeyTable,  
 colsa.name AS PKeyColumn,  
 CASE is_disabled WHEN 1 THEN 'YES'  
 WHEN 0 THEN 'No' END AS IsDisabled,  
 delete_referential_action_desc AS DeleteRule,  
 update_referential_action_desc AS UpdateRule  
 FROM sys.foreign_key_columns FKeyC  
 INNER JOIN sys.foreign_keys fkey  
 ON fkeyc.parent_object_id = fkey.parent_object_id  
 AND fkeyc.referenced_object_id = fkey.referenced_object_id  
 INNER JOIN sys.all_columns cols  
 ON fkeyc.parent_object_id = cols.object_id  
 AND parent_column_id = cols.column_id  
 INNER JOIN sys.all_columns colsa  
 ON fkeyc.referenced_object_id = colsa.object_id  
 AND parent_column_id = colsa.column_id  
 ORDER BY is_disabled DESC, delete_referential_action_desc, update_referential_action_desc  

You may also add create_date and modify_date in the select list for creation and modification date of respective foreign key. Once the list is available, analyze the requirement and configured rule along with disabled foreign keys.

Analyze all foreign keys in your SQL Server database

  • Apr 07 / 2011
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get code of user stored procedures and UDF in a database

If one is required to search for any column or hard coded value in SP/UDF code then access to code definition is required. So here is a script to generate code of all SP and UDF in a database. This script may be utilize to

  • Check for any specific column or hard coded value in all SP/UDF
  • Check for dependency of any object by providing the object name as like parameter for code

Following script would generate all user SP along with their code.

 
USE [DB_Name_here]  
 GO  
 SELECT ROUTINE_NAME [SP Name], routine_definition [SP Code]  
 FROM INFORMATION_SCHEMA.ROUTINES r INNER JOIN sys.[objects] o  
 ON r.[ROUTINE_NAME] = o.[name]  
 WHERE TYPE='P'  
 AND [is_ms_shipped] = 0  
 ORDER BY routine_name  
 GO  

And with a little modification of above script we may also get list and code of all UDF in a database.

 USE [DB_Name_here]  
 GO  
 SELECT ROUTINE_NAME [UDF Name], routine_definition [UDF Code], type AS [UDF Type]  
 FROM INFORMATION_SCHEMA.ROUTINES r INNER JOIN sys.[objects] o  
 ON r.[ROUTINE_NAME] = o.[name]  
 WHERE [is_ms_shipped] = 0  
 AND type in ('FN','TF','IF')  
 ORDER BY type,routine_name  
 GO  

Do not forget to provide proper database name in use database statement.

  • Apr 06 / 2011
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get list of Stored procedures or UDF with creation and modification dates

For monitoring and analysis purpose a broad view about creation and modification of SP and UDF in a database was required. I have created two scripts to get all SP and UDF with their creation and last modification dates.

/*  
 Get all User SP along with Creation and last modification dates  
 */  
 USE [DB_Name_here]  
 GO  
 SELECT name, create_date, modify_date  
 FROM sys.[objects]   
 WHERE type = 'P'  
 AND [is_ms_shipped] = 0  
 ORDER BY [create_date] desc, modify_date DESC   
 GO  

And in case of UDF they may be scalar, table valued or in-line table valued. You may generate the list of any type by providing following parameters for type column.
Scalar UDF = FN
Table valued = TF
In-Line Table Valued = IF

In following script i have included all three types in list of UDF.

/*  
 Get all UDF along with Creation and last modification dates  
 */  
 USE [DB_Name_here]  
 GO  
 SELECT name, create_date, modify_date, type  
 FROM sys.[objects]   
 WHERE type in ('FN','TF','IF')  
 AND [is_ms_shipped] = 0  
 ORDER BY type,[create_date] desc, modify_date DESC   
 GO  

For both of above scripts do not forget to provide/select required database name. Table used in these scripts is sys.Objects. Click here to get further details of columns and parameters in this table.

  • Mar 17 / 2011
  • 1
dbDigger, Security and Permissions, T-SQL Scripts

Generate permissions for a login in all databases

In one of my previous posts i shared a script to generate all permissions for a database. Couple of days back a blog reader commented on post and asked for a script that may generate permissions for a specific login in all databases. Such script would be helpful for servers with many databases. I have tried to accomplish the task by combining the previously posted script with some other procedures.
Whole script is composed of 6 steps/modules. Let us have a look at 6 steps that together build whole script.

  1. Create a procedure (USP_GeneratePerm) to generate permissions
  2. Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases
  3. Execute USP_CreateInAllDBs with sp_MSForEachDB
  4. Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases
  5. Execute USP_ExecuteInAll
  6. Housekeeping

As we can see in above steps that three stored procedures are created and executed.  Last step would be to drop the created procedures.

  • Prepare your mind that this script may need some modifications according to your scenario. Especially the statements where system/READ ONLY databases are need to excluded from the databases in which our sp would be executed.
  • Script would not be generated for READ ONLY databases. So for READ ONLY databases permissions may be generated separately.
  • Rather than to execute whole script at a time. Execute each of six steps isolated.
Now we would go through the brief description of above mentioned 6 steps

Create a procedure (USP_GeneratePerm) to generate permissions

A stored procedure would be created in master database. Procedure USP_GeneratePerm would be accept two parameters for database name and login. It would actually generate the permission statements.

Create a procedure (USP_CreateInAllDBs) to create USP_GeneratePerm in all databases

The procedure USP_generatePerm is required to be created in all databases other than system and READ ONLY databases. So to complete this requirement we have to create another procedure USP_Create InAllDBs. It would also except two parameters. One is name of database where primary procedure would be created and second is name of primary procedure. In our case primary procedure is  USP_GeneratePerm.

 Execute USP_CreateInAllDBs with sp_MSForEachDB

In third step we would execute USP_CreateInAllDBs in all database by using sp_MSForEachDB system stored procedure. I have exclude the system databases in script.

Create a procedure (USP_ExecuteInAll) to execute USP_GeneratePerm in all databases

No we have our primary SP created in all databases other than ssytem and READ ONLY databases. Now it is required to execute the primary SP in all databases. For this purpose we would create another SP named USP_ExecuteInAll. It would accept one parameter that is login for which script is required to be generated.

Execute USP_ExecuteInAll

Execute the above created USP_ExecuteInAll by providing login as parameter.

Housekeeping

Drop the procedures created in mater and other databases.
According to above descriptions, it is obvious that login for which permissions are required to be generated is provided in step # 5 i.e. Execute USP_ExecuteInAll.
Click here to download the script file. If any one could help to further improve the script and reduce its complexity then you are welcome with due credit on this blog.

Consult us to explore the Databases. Contact us