Tag Archives: SQL

Query to fetch table sizes and row counts quickly

Here’s a query that comes in handy when checking the size of your tables within a SQL Server Database:

SELECT st.name AS TableName,
	(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id) AS RowLength,
	si.rowcnt AS [RowCount],
	CAST(si.rowcnt*(SELECT SUM(max_length) FROM sys.columns WHERE object_id=st.object_id)/1024.0/1024.0 AS INT) AS MaxTableSizeMB
FROM sys.tables as st
LEFT JOIN sysindexes si on st.object_id=si.id and si.indid < 2
ORDER BY st.name

SQL Server login with read access on every database

Here’s a quick script to add a new SQL server login with read access to every database on the server:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_adduser [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_datareader'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'EXEC sp_addrolemember ''db_denydatawriter'',''AndreasReader''' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

For newer versions of SQL Server, use this:

USE [master]
GO
 
CREATE LOGIN [AndreasReader] WITH PASSWORD=N'AndreasPasswordOhlalala12345'
GO
 
SELECT 
'USE ['+ name +']' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'CREATE USER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_datareader] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 
'ALTER ROLE [db_denydatawriter] ADD MEMBER [AndreasReader]' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.databases

Be sure to also add to the “model” database. That way you will also have read access to newly created databases.