Modern Data Insight

Following article shows how to use external text file in SQL server. But you have to remember the speed issue in using a text file compared to indexed table. Connect to Text Files with the Microsoft Jet ODBC Text Driver

SQL bcp Exprot to CSV: Following process can be used to create csv from sql server with bcp command. First of all we need a format file and then bcp command in stored proc or run directly from query analyzer. Sample Format File 7.0 [sql bcp version] 305 [number of data fields] 1 SQLCHAR 0 9 "," 1 MyField Exec master..xp_cmdshell bcp "SELECT * FROM ##WrkData_LBW_Exp_2" queryout ' + [export file].csv + ' -f\\[format file].fmt -U [user name] -P [pasword] reference: bcp

BCP Basics : bcp sql command can be used to import data. This process is faster than dts. bcp can be used with format file or data can directly imported to a pre-defined table. Format file is the key in this process. Code : BULK INSERT tempdb..U_LNBASE_IMPORT_LNKEYS FROM '\\dfw-iapp-fsnt51\wiredata\loans.txt' WITH ( FIELDTERMINATOR = '"', ROWTERMINATOR = '\n' )

SQL Table Definition : This sql query is used to find table definition without using enterprise manager table design. Code : SELECT a.[name] as 'Table', b.[name] as 'Column', c.[name] as 'Datatype', b.[length] as 'Length', CASE WHEN b.[cdefault] > 0 THEN d.[text] ELSE NULL END as 'Default', CASE WHEN b.[isnullable] = 0 THEN 'No' ELSE 'Yes' END as 'Nullable' FROM sysobjects a INNER JOIN syscolumns b ON a.[id] = b.[id] INNER JOIN systypes c ON b.[xtype] = c.[xtype] LEFT JOIN syscomments d ON b.[cdefault] = d.[id] WHERE a.[xtype] = 'u' -- 'u' for user tables, 'v' for views. and a.[name]='table name' AND a.[name] <> 'dtproperties' ORDER BY a.[name],b.[colorder]

SQL Table Space Script : This sql query uses "sp_mstablespace" stored procedure to get table space for all the user table in the server. Code : SELECT a.[name] as 'Table', (select convert(int, rowcnt) FROM dbo.sysindexes WHERE indid < 2 and id=a.id) as rows, (((SELECT sum(dpages) FROM dbo.sysindexes WHERE indid < 2 and id = a.id) + (SELECT isnull(sum(used), 0) FROM dbo.sysindexes WHERE indid = 255 and id = a.id))*8) as DataSpaceUsed, (((SELECT sum(used) FROM dbo.sysindexes WHERE indid in (0, 1, 255) and id = a.id) - ((SELECT sum(dpages) FROM dbo.sysindexes WHERE indid < 2 and id = a.id) + (SELECT isnull(sum(used), 0) FROM dbo.sysindexes WHERE indid = 255 and id = a.id)) )*8) as IndexSpaceUsed FROM sysobjects a WHERE a.[xtype] = 'u' AND a.[name] <> 'dtproperties' ORDER BY a.[name]

How to find all the user tables in a database : select * from sysobjects a where a.[xtype] = 'u' and a.[name] <> 'dtproperties' . This will give you list of all your tables.