Wednesday, December 08, 2010
SQL Server Security Audit Report
USE master
GO
SET nocount ON
-- Get all roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole
-- sp_help syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))
DECLARE @ServerRole VARCHAR(128)
DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END
CLOSE srv_role
DEALLOCATE srv_role
SELECT ServerRole, MemberName FROM #temp_memberrole
-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'
SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END
DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole
-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1
-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)
DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin
PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd
SET ROWCOUNT 8
DELETE FROM #temp_groupadmin
SET ROWCOUNT 0
INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')
FETCH NEXT FROM grp_role INTO @grpname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_groupadmin2
DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2
PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''
-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))
DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)
DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'
PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)
INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember
FETCH NEXT FROM grp_role INTO @dbname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_rolemember_final
DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final
GO
SET nocount ON
-- Get all roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole
-- sp_help syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))
DECLARE @ServerRole VARCHAR(128)
DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END
CLOSE srv_role
DEALLOCATE srv_role
SELECT ServerRole, MemberName FROM #temp_memberrole
-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'
SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END
DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole
-- Get individual Logins
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Get Group logins
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1
-- get group list
-- EXEC xp_cmdshell 'net group "AnalyticsDev" /domain'
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)
DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin
PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd
SET ROWCOUNT 8
DELETE FROM #temp_groupadmin
SET ROWCOUNT 0
INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')
FETCH NEXT FROM grp_role INTO @grpname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_groupadmin2
DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2
PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''
-- Get all the Database Rols for that specIFic members
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))
DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)
DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'
PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)
INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember
FETCH NEXT FROM grp_role INTO @dbname
END
CLOSE grp_role
DEALLOCATE grp_role
SELECT * FROM #temp_rolemember_final
DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final
Monday, December 06, 2010
Thursday, December 02, 2010
SQL Server Encryption - Encrypt a database
USE master;
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go
USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO
USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO
SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go
USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO
USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO
SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)
SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys
Tuesday, November 30, 2010
SQL 2008 Server Audit - Server and Database Specification
--Create Audit
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO
--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)
--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)
--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)
CREATE SERVER AUDIT [DDL-Audit]
TO FILE
( FILEPATH = N'E:\Audit\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '877f038b-d57c-44d1-8db4-c2c3dc1321a1'
)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_ACCESS_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (AUDIT_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_CHANGE_GROUP),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SERVER_OBJECT_CHANGE_GROUP),
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
ADD (SERVER_STATE_CHANGE_GROUP),
ADD (SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Create Audit specifics Server Level
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout]
FOR SERVER AUDIT [Login-Logout-Audit]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (LOGOUT_GROUP)
WITH (STATE = ON)
GO
--Create Audit Specific Database Level
CREATE DATABASE AUDIT SPECIFICATION [DDL_changes_audit]
FOR SERVER AUDIT [DDL-Audit]
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO
--Read from Audit Trace File
select * FROM sys.fn_get_audit_file('E:\Audit\DDL*',default,default)
--Disable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = OFF)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = OFF)
--Enable Audit and Server Audit Specification
ALTER SERVER AUDIT [DDL-Audit] with (State = ON)
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-Login-Logout] with (State = ON)
Thursday, November 18, 2010
Jobs on Server and detail information
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
--where x.running = 1
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[Temp1]')
)
DROP TABLE [tempdb].[dbo].[Temp1]
GO
CREATE TABLE [tempdb].[dbo].[Temp1]
(
job_id uniqueidentifier NOT NULL,
last_run_date nvarchar (20) NOT NULL,
last_run_time nvarchar (20) NOT NULL,
next_run_date nvarchar (20) NOT NULL,
next_run_time nvarchar (20) NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname
COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)
DECLARE @job_owner sysname
DECLARE @is_sysadmin INT
SET @is_sysadmin = isnull (is_srvrolemember ('sysadmin'), 0)
SET @job_owner = suser_sname ()
INSERT INTO [tempdb].[dbo].[Temp1]
--EXECUTE sys.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE [tempdb].[dbo].[Temp1]
SET last_run_time = right ('000000' + last_run_time, 6),
next_run_time = right ('000000' + next_run_time, 6);
-----
SELECT j.name AS JobName,
j.enabled AS Enabled,
CASE x.running
WHEN 1
THEN
'Running'
ELSE
CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END
AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE
WHEN x.last_run_date > 0
THEN
convert (datetime,
substring (x.last_run_date, 1, 4)
+ '-'
+ substring (x.last_run_date, 5, 2)
+ '-'
+ substring (x.last_run_date, 7, 2)
+ ' '
+ substring (x.last_run_time, 1, 2)
+ ':'
+ substring (x.last_run_time, 3, 2)
+ ':'
+ substring (x.last_run_time, 5, 2)
+ '.000',
121
)
ELSE
NULL
END
AS LastRunTime,
CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END
AS LastRunOutcome,
CASE
WHEN h.run_duration > 0
THEN
(h.run_duration / 1000000) * (3600 * 24)
+ (h.run_duration / 10000 % 100) * 3600
+ (h.run_duration / 100 % 100) * 60
+ (h.run_duration % 100)
ELSE
NULL
END
AS LastRunDuration
FROM [tempdb].[dbo].[Temp1] x
LEFT JOIN
msdb.dbo.sysjobs j
ON x.job_id = j.job_id
LEFT OUTER JOIN
msdb.dbo.syscategories c
ON j.category_id = c.category_id
LEFT OUTER JOIN
msdb.dbo.sysjobhistory h
ON x.job_id = h.job_id
AND x.last_run_date = h.run_date
AND x.last_run_time = h.run_time
AND h.step_id = 0
--where x.running = 1
Monday, November 15, 2010
Friday, November 12, 2010
Thursday, November 04, 2010
Thursday, October 28, 2010
Getting a SQL Server RowCount Without doing a Table Scan
SELECT [name]
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO
----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO
CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2
RETURN @nRowCount
END
GO
, dbo.[fn_get_table_rowcount] ([name]) as [Row Count]
FROM sysobjects
WHERE type='U' and name != 'dtproperties'
ORDER BY [Row Count] desc
GO
----------------------------------------------------------------------
--FUNCIOTN
----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_get_table_rowcount]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_get_table_rowcount]
GO
CREATE FUNCTION dbo.[fn_get_table_rowcount]
(
@sTableName sysname -- Table to retrieve Row Count
)
RETURNS INT -- Row count of the table, NULL if not found.
AS BEGIN
DECLARE @nRowCount INT -- the rows
DECLARE @nObjectID int -- Object ID
SET @nObjectID = OBJECT_ID(@sTableName)
-- Object might not be found
IF @nObjectID is null RETURN NULL
SELECT TOP 1 @nRowCount = rows
FROM sysindexes
WHERE id = @nObjectID AND indid < 2
RETURN @nRowCount
END
GO
Subscribe to:
Posts (Atom)