Sub ConnectSqlServer()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim squery As String
' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=RCHNLPT136\SQLEXPRESS2014;" & _
"Initial Catalog=ExpenseMgmt;" & _
"user id=sa;password=Welcome@123;" & _
"Integrated Security=SSPI;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection and execute.
conn.Open sConnString
squery = "select " & _
" tb.TABLE_SCHEMA as TableSchema, tb.TABLE_NAME as TableName," & _
" sum(Case When DATA_TYPE = 'int' then 4 else 0 end + Case When DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH > 0 then CHARACTER_MAXIMUM_LENGTH * 2 When DATA_TYPE = 'nvarchar' and CHARACTER_MAXIMUM_LENGTH < 0 then" & _
" 8000 else 0 end + Case When DATA_TYPE = 'varchar' then CHARACTER_MAXIMUM_LENGTH else 0 end + Case When DATA_TYPE = 'Char' then CHARACTER_MAXIMUM_LENGTH else 0 end + Case When DATA_TYPE = 'nchar' then CHARACTER_MAXIMUM_LENGTH * 2 else 0 end + Case When DATA_TYPE = 'decimal' then 8 else 0 end + Case When DATA_TYPE = 'bit' then 1 else 0 end + Case When DATA_TYPE = 'Tinyint' then 1 else 0 end + Case When DATA_TYPE = 'Smallint' then 2 else 0 end + Case When DATA_TYPE = 'Smallint' then 4 else 0 end + Case When DATA_TYPE = 'Smalldatetime' then 4 else 0 end + Case When DATA_TYPE = 'Smallmoney' then 4 else 0 end + Case When DATA_TYPE = 'Bigint' then 8 else 0 end + Case When DATA_TYPE = 'Datetime' then 8 else 0 end + Case When DATA_TYPE = 'Datetime2' then 8 else 0 end + Case When DATA_TYPE = 'Float' then 8 else 0 end + Case When DATA_TYPE = 'Money' then 8 else 0 end + Case When DATA_TYPE = 'Numeric' and Numeric_Precision > 18 then 16 else 0 end +" & _
" Case When DATA_TYPE = 'Numeric' and Numeric_Precision <= 18 then 8 else 0 end + Case When DATA_TYPE = 'Time' then 8 else 0 end + Case When DATA_TYPE = 'TimeStamp' then 0 else 0 end + Case When DATA_TYPE = 'Uniqueidentifier' then 16 else 0 end ) Size_of_Row from INFORMATION_SCHEMA.COLUMNS c,INFORMATION_SCHEMA.TABLES tb where c.TABLE_NAME =tb.TABLE_NAME group by tb.TABLE_NAME,tb.TABLE_SCHEMA"
Set rs = conn.Execute(squery)
' Check we have data.
If Not rs.EOF Then
' Transfer result.
Sheets(1).Range("A1").CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub
This entry is in response to the Conserve, Recycle and Discover contest organised by blogadda.com in association with Pringoo ! World Environment Day, commemorated on 5 June since 1972, is one of the ways focuses world attention on the environment and encourages political action. People from countries all over the world have mobilized for individual and organized environmental action. Activities involve all sectors of society, governments, non- and inter-governmental organizations, businesses, industries, civil society, media and schools. Please take the following Pledge to save our environment. 1, I pledge to Recycle more, use less water and electricity. 2, I pledge to smoke less in 2010. 3, I pledge to send more electronic documents instead of using paper. 4, I pledge to continue using my bicycle to go to work (instead of my motorbike). 5, wash with full loads it is more energy efficient than doing several smaller ones. 6, Incite of dryer I will use hang dry for energy sav...
Comments