Use AdventureWorks GO SELECT Name, Modify_date FROM sys.tables WHERE name = 'ProductPhoto' GO
It is important to understand that modification date means modification in structure or properties of a table. Any operation on data of table does not have any link to modification date of that table that is being retrieved through this script. And to get this information for all of your tables in a database, just remove the where clause.
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'
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.
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
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.
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.
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.
It is important to mention that in case of permissions Column-level permissions override object-level permissions on the same entity.