:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Mar 26 / 2009
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

get list of column meta data in a table from INFORMATION_SCHEMA

To day some one asked me that how to get columns count in a table. For information like list of columns of a table, i mostly used sysobjects, syscolumns and systypes by joining these. During a little googling i find that meta data about columns of a table can be efficiently and easily found in system views like INFORMATION_SCHEMA.Columns. For example following script may generate a list of columns in a table along with there relevanmt meta data

 
SELECT  
 table_name AS [TABLE Name],  
 column_name AS [COLUMN Name],  
 data_type AS [COLUMN DATA TYPE],  
 character_maximum_length AS [MAX length],  
 is_nullable as[Nullable],  
 ordinal_position AS [Postion IN TABLE]  
 FROM INFORMATION_SCHEMA.Columns  
 WHERE table_name = 'tableNamehere'  
 ORDER BY ordinal_position  

Columns aliases are self explanatory. Above script may be modified through altering parameters in select, where and order by clauses. Using such system views is far more easy and efficient than to use system tables.
And also to get count of columns in a table i just modified the script to simpler form as

 
SELECT count(column_name)  
 FROM INFORMATION_SCHEMA.Columns  
 WHERE table_name = 'tableNameHere'  
  • Mar 25 / 2009
  • 0
dbDigger, Monitoring and Analysis, T-SQL Scripts

Get list of all current connections to SQL Server instance

List of all current connections to SQL Server instance can be found in SSMS or Enterprise Manager. But these tools are not always accessible and also expert DBAs like to generate lists by code as it is flexible and easy to analyze. So to get list of all current connections to SQL Server instance use following script.

 
SELECT DB_NAME(dbid)AS ConnectedToDB,  
 hostname, program_name,loginame,  
 cpu, physical_io, memusage, login_time,  
 last_batch, [status]  
 FROM master.dbo.sysprocesses  
 ORDER BY dbid, login_time, last_batch  
 GO  

Also you can filter the results for a single user or a single database by providing filter criteria in where clause as required. For example to get just connections to database you may add following condition in where clause

 
WHERE dbid = DB_ID ('AdventureWorks')  

Similarly you may filter the results with relevance to any column as required. Above script can be used on all versions of SQL Server.

  • Mar 19 / 2009
  • 0
Constraints and Keys, dbDigger, T-SQL Scripts

Bind or Unbind a rule through sp_bind and sp_unbind

RULES are SQL Server objects used to implement restrictions and checks on data in a column. A single created RULE may be bind to many columns. Suppose we have a RULE created. This rules specify that minimum DOB of an employee being entered should always be greater than or equal to 18 years. Now once RULE is created then it can be bind to many columns of date of birth in different tables. And all bind columns will accept date of birth greater than or equal to 18 years of age.
To bind a column DOB in Employees table following is syntax

Use Databasename
GO

EXEC sp_bindrule ‘MinDOB’, ‘Employees.DOB’
GO

Similarly same RULE may be bind to as many columns in a database as required. Now in order to drop a RULE, it should be first unbind from all bind columns. So to unbind MinDOB from [Employees].[DOB] following is the syntax

Use Databasename
GO

EXEC sp_unbindrule ‘Employees.DOB’
GO

For details of creating and using rules and defaults in SQL Server please read my article on MSSQLTips.com

  • Mar 06 / 2009
  • 3
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Generate script for all permissions in a database

In many scenarios DBA is required to configure permissions of a user. If permissions are to be re configured for an existing user, due to user deletion or some other reason, then it may be a nightmare to exactly configure the same. A handy solution is that you may generate the permissions for your users regularly in a usable form and use it when ever required. Following script will serve the same purpose. T-SQL for Permissions may be generated by following script. This T-SQL may be used later with a single click whenever required. The script will be used on SQL Server 2005 and later

 
Use AdventureWorks  
 GO  
 SELECT  
 state_desc + ' ' + permission_name +  
 ' on ['+ SYS.SCHEMAS.name + '].[' + SYS.OBJECTS.name + ']  
 to [' + SYS.DATABASE_PRINCIPALS.name + ']'  
 COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]  
 FROM SYS.DATABASE_PERMISSIONS JOIN sys.objects  
 ON SYS.DATABASE_PERMISSIONS.major_id = sys.objects.OBJECT_ID  
 JOIN SYS.SCHEMAS  
 ON SYS.OBJECTS.SCHEMA_ID = SYS.SCHEMAS.SCHEMA_ID  
 JOIN SYS.DATABASE_PRINCIPALS  
 ON SYS.DATABASE_PERMISSIONS.grantee_principal_id = SYS.DATABASE_PRINCIPALS.principal_id  
 order by [Permissions T-SQL]  
 GO  

each and every permission will be provided in T-SQL form for every user. Whole result may be saved to use later.

T-SQL for permissions

If you require to generate permissions for a single user or single object then conditions may be applied through where clause in our script.

  • For specific object put [SYS.SCHEMAS.name] in where clause
  • For specific user put [SYS.DATABASE_PRINCIPALS.name] in where clause

To get all permissions for a login in all databases please read my other post.

  • Mar 04 / 2009
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Get permissions for SQL Server Database users

As a part of best practices implementation, some times i require to review and audit the rights of database users in a specific database. I have created following script to get list of permissions for all database users in a database. This script will work for SQL Server 2005 and onwards. I will run it on AdventureWorks

 
Use AdventureWorks  
 GO  
 SELECT USER_NAME(grantee_principal_id) AS [UserName],  
 OBJECT_NAME(major_id) AS ObjectName,  
 permission_name AS PermissionName,  
 state_desc AS PermissionStatus  
 FROM SYS.DATABASE_PERMISSIONS  
 WHERE class = 1  
 ORDER BY userName,permissionstatus  
 GO  

The result will be produced as shown a part in following snap.

Permissions for DB Users

It is important to mention that in case of permissions Column-level permissions override object-level permissions on the same entity.

Consult us to explore the Databases. Contact us