Monday 11 April 2011

When does my SQL Server support end ?

SQL Server VersionRelease DateMainstream Support EndExtended Support EndLink to Microsoft Support Lifecycle
SQL 703-01-199912-31-200501-11-2011Support Lifecycle
SQL 200011-30-200004-08-200804-09-2013Support Lifecycle
SQL 200501-14-200604-12-201104-12-2016Support Lifecycle
SQL 200811-06-200801-14-201401-08-2019Support Lifecycle
SQL 2008 R207-20-201001-14-201401-08-2019Support Lifecycle

The Support Lifecycle links will have more detailed information about Service Packs and support guidelines for the specific versions.

Query to find Service account of SQL Server

DECLARE @serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'
SELECT @Serviceaccount

How to retrieve table name and privilages of user in SQL server

Select TABLE_NAME, PRIVILEGE_TYPE from INFORMATION_SCHEMA.TABLE_PRIVILEGES where
GRANTEE = 'Toolbox'

Query to find the job history in SQL Server

select a.run_date,b.name,a.run_duration,run_time,a.server  from sysjobhistory a inner join sysjobs b on a.job_id=b.job_id where a.job_id='8E7D12F1-6B7E-4C08-8063-D8AAFD99F267' and a.step_id =0  order by a.run_date desc

Query to find the object permission for specific objects

select
sys.database_permissions.state_desc + ' ' + sys.database_permissions.permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
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
where sys.objects.name='object_name’

How to find table count of all tables in a database in sql

How to find table count of all tables in a database


SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"

FROM sysobjects o, sysindexes i

WHERE i.id = o.id

AND indid IN(0,1)

AND xtype = 'u'

AND o.name <> 'sysdiagrams'

ORDER BY i.rowcnt DESC