Dropping list of table at a time

I dont know you are femilier with this statement of not, But you can drop selected tables by issueing one drop statement as below.

DROP TABLE Table1,Tbl2,MyTab

Get all tables information in the database

Some times we may think to see single table information mainly the row count and will get the result easily by typing sp_spaceused tableName.
But how to get all the tables information within the database. Please have this script.
DECLARE @SpaceUsed TABLE
(
 TableName VARCHAR(100)
 ,No_Of_Rows BIGINT
 ,ReservedSpace VARCHAR(15)
 ,DataSpace VARCHAR(15)
 ,Index_Size VARCHAR(15)
 ,UnUsed_Space VARCHAR(15)
 )
DECLARE @sqlstring VARCHAR(500)
SET @sqlstring  =  'exec sp_spaceused ''?'''
INSERT INTO @SpaceUsed EXEC sp_msforeachtable @command1=@sqlstring
SELECT * FROM @SpaceUsed
Of course, you could use the following too. But always depends on the format you want the cost to execute.
DECLARE @sqlstring VARCHAR(500)
 SET @sqlstring = 'exec sp_spaceused ''?'''
 EXEC sp_msforeachtable @command1=@sqlstring

Get DB roles

DECLARE @database nVARCHAR(128)=NULL
DECLARE @user VARCHAR(20)=NULL
DECLARE @dbo CHAR(1)=NULL
DECLARE @access CHAR(1)=NULL
DECLARE @security CHAR(1)=NULL
DECLARE @ddl CHAR(1)=NULL
DECLARE @datareader CHAR(1)=NULL
DECLARE @datawriter CHAR(1)=NULL
DECLARE @denyread CHAR(1)=NULL
DECLARE @denywrite CHAR(1)=NULL
DECLARE @dbname VARCHAR(200)
DECLARE @sqlstr VARCHAR(8000)
CREATE TABLE #DBROLES (
DBName SYSNAME NOT NULL,
UserName SYSNAME NOT NULL,
db_owner VARCHAR(3) NOT NULL,
db_accessadmin VARCHAR(3) NOT NULL,
db_securityadmin VARCHAR(3) NOT NULL,
db_ddladmin VARCHAR(3) NOT NULL,
db_datareader VARCHAR(3) NOT NULL,
db_datawriter VARCHAR(3) NOT NULL,
db_denydatareader VARCHAR(3) NOT NULL,
db_denydatawriter VARCHAR(3) NOT NULL,
Cur_Date DATETIME NOT NULL DEFAULT GETDATE()
)
DECLARE DBName_Cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name in ('master')
ORDER BY name
OPEN DBName_Cursor
FETCH NEXT FROM DBName_Cursor INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstr = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '
+''''+@dbName +''''+ ' as DBName ,UserName, '+CHAR(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
FROM (
SELECT b.name as USERName, c.name as RoleName
FROM '
+ @dbName+'.dbo.sysmembers a '+CHAR(13)+
' JOIN '+ @dbName+'.dbo.sysusers b '+CHAR(13)+
' on a.memberuid = b.uid JOIN '+@dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
GROUP BY USERName
ORDER BY UserName'
--Print @sqlstr
Execute (@sqlstr)
FETCH NEXT FROM DBName_Cursor INTO @dbname
END
CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
SELECT * FROM #DBRoles
WHERE ((@database is NULL) OR (DBName LIKE '%'+@database+'%')) AND
((@user is NULL) OR (UserName LIKE '%'+@user+'%')) AND
((@dbo is NULL) OR (db_owner = 'Yes')) AND
((@access is NULL) OR (db_accessadmin = 'Yes')) AND
((@security is NULL) OR (db_securityadmin = 'Yes')) AND
((@ddl is NULL) OR (db_ddladmin = 'Yes')) AND
((@datareader is NULL) OR (db_datareader = 'Yes')) AND
((@datawriter is NULL) OR (db_datawriter = 'Yes')) AND
((@denyread is NULL) OR (db_denydatareader = 'Yes')) AND
((@denywrite is NULL) OR (db_denydatawriter = 'Yes'))
ORDER BY UserName
DROP TABLE #DBROLES

SSIS Package Error while exporting data to EXCEL

I have been told to create a SSIS package which populate the data in Excel and send to the requested users.
I have succeded creating every task in the process. But, while executing the pacage I have got below error. So after little bit investigation I used the below solution and made it work. It may helpful to one of you.

If you have some other issue as you can see many, Please send me the issue I will try to make it work for you.

Problem :

Error: 0xC00F9304 at SQLToExcel, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Error: 0xC020801C at Data Flow Task, ReportExcel [16]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "ReportExcel" (16) failed validation and returned error code 0xC020801C.
Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
SSIS package "SQLToExcel.dtsx" finished: Failure.

Solution :

The below solution may solve your problem.

Right click on Project
Select Debugging
In Debug Options
Run64BitRuntime make it to False.

System View Permissions

Select permissions on system views for regular user
GRANT VIEW SERVER STATE TO LoginName

Deny permissions on system views for regular user
DENY VIEW SERVER STATE TO LoginName