mysql cheat sheet

-- file: mssql-cheat-sheet.sql

-- how-to get column meta data on sql server 2008

-- get columns meta data 

SELECT top 100000

 INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG              AS 'DataBaseName' 

,INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA               AS 'SchemaName'

,SysObjects.NAME                                       AS 'TableName'

,sys.all_columns.NAME                                  AS 'ColumnName'

,INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION           AS 'OrdinalPosition'

,sys.types.name                                        AS 'DataType' 

,sys.all_columns.is_nullable                           AS 'IsNullable' 

,sys.all_columns.is_identity                           AS 'IsPrimaryKey'  

,sys.all_columns.max_length                            AS 'MaxLength' 

,sys.all_columns.is_computed                           AS 'IsComputed' 

,syscomments.text                                      AS 'DefaultValue'

FROM dbo.sysobjects  

INNER JOIN sys.all_columns 

   ON ( SysObjects.id = sys.all_columns.object_id )

INNER JOIN sys.types 

   on ( sys.types.system_type_id = sys.all_columns.system_type_id )

LEFT JOIN syscomments  

   on ( SysObjects.id = syscomments.id ) 

-- LEFT JOIN sys.tables stb on sc.object_id = SysObjects.parent_obj

LEFT JOIN INFORMATION_SCHEMA.COLUMNS  

on  (     INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=SysObjects.NAME 

      and sys.all_columns.NAME = INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME ) 

--LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  

--inner join sys.types st

--on sc.xtype = st.system_type_id

WHERE 1=1 

AND SysObjects.xtype = 'U'  

and sys.types.name <> 'sysname' --to a bug with this one occurs if enabled 

and sys.all_columns.is_computed = 0 --todo a bug with this one occurs if enabled 

-- and SysObjects.name = @TableName --uncomment this line while debugging

-- AND sys.all_columns.name LIKE '%TABLE_NAME_TO_SRCH%'

ORDER BY DatabaseName , SchemaName , TableName , INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION

; 

-- how-to search for a table 

select * from sys.tables

where 1=1

and name like '%consol%' 

; 

-- WHERE AM I ... 

SELECT

   SERVERPROPERTY('MachineName' )                  AS "ServerName"

 , SERVERPROPERTY('ServerName')                    AS "ServerInstanceName"

 , SERVERPROPERTY('InstanceName')                  AS "Instance"

 , SERVERPROPERTY('ProductVersion')                AS "ProductVersion"

 , Left(@@Version, Charindex('-', @@version) - 2)  As "VersionName"

 , db_name()                                       as "DB_NAME"

 -- , host_name()                                     AS "HOST_NAME"

 , SUSER_NAME()                                    AS "LOGGED_USER"

 ; 

-- HOW-MUCH FULL TEXT CATALOGS ARE IN THE CURRENT DB 

select count(*)                                    AS "FULLTEXT_CATALOGS_COUNT" 

From sys.fulltext_catalogs

; 

--how-to geneate select columns list from table

declare @TABLE_NAME VARCHAR(100)

SELECT @TABLE_NAME = 'CONSOLIDATE_ASST_LIAB_balance'

SELECT 

CASE 

   WHEN C.ORDINAL_POSITION = 1 then ' ; SELECT    

   ' ELSE ' , '

END

 + '[' + T.TABLE_NAME + ']'

 + '.' + '[' + C.COLUMN_NAME + ']'

 AS "SQL_CODE"

FROM INFORMATION_SCHEMA.TABLES T

INNER JOIN INFORMATION_SCHEMA.COLUMNS C 

  ON ( T.TABLE_NAME=C.TABLE_NAME )

WHERE  1=1

   AND T.TABLE_TYPE='BASE TABLE'

   AND T.TABLE_NAME = @TABLE_NAME

--ORDER BY C.ORDINAL_POSITION ASC

UNION ALL

SELECT

' FROM '+ @TABLE_NAME AS "SQL_CODE"

UNION ALL

SELECT

' WHERE 1=1 

' AS "SQL_CODE"

UNION ALL 

SELECT 

 + '-- AND [' + T.TABLE_NAME + ']'

 + '.' + '[' + C.COLUMN_NAME + ']'

 AS "SQL_CODE"

FROM INFORMATION_SCHEMA.TABLES T

INNER JOIN INFORMATION_SCHEMA.COLUMNS C 

  ON ( T.TABLE_NAME=C.TABLE_NAME )

WHERE  1=1

   AND T.TABLE_TYPE='BASE TABLE'

   AND T.TABLE_NAME = @TABLE_NAME

--ORDER BY C.ORDINAL_POSITION ASC

; 

-- VersionHistory

-- 1.0.0 -- 2015-03-18 09:31:31 -- ysg -- Init